This is the first post in (hopefully) a series of posts on handy formulas that you can use to codelessly boost your forms' functionality.
A common question that comes up on our forums and elsewhere is: How do I round a number to the nearest [some number]?
XPath provides the useful round() function, but that only allows rounding to the nearest whole number. What if you want to round to the nearest 100, or 5, or half?
Luckily, round() enables us to use a simple formula to do this:
Round to the nearest N
round(value div N) * N
Here, value can be a single field in your form, or an entire formula whose result you want to round.
So to round to the nearest 100:
round(value div 100) * 100
to round to the nearest 5:
round(value div 5) * 5
to round to the nearest 1/3:
round(value div (1 div 3)) * (1 div 3)
(or mathematically simplified):
round(value * 3) div 3
It's that simple.
Another question that often comes up is how to round a value to N decimal places. To do this, we can just apply the same concept:
Round to N decimal places:
round(value div 10-N) * 10-N
which can be simplified to:
round(value * 10N) div 10N
So to round a value to 2 decimal places, you would use this:
round(value * 100) div 100
to round a value to 4 decimal places:
round(value * 10000) div 10000
As a final note, occasionally people want to round a value up or down instead of to the closest number. This is just as simple. Simply modify the above formulas to replace round with ceiling to round up or floor to round down:
Round up to the nearest 100
ceiling(value div 100) * 100
Round down to the nearest 100
floor(value div 100) * 100
That's it! Enjoy!