Round Number Up or Down

[#1]

Hello

I have an option in Excel to round the number by significance number

for example, I have numbers as 13.24 or 0.57, and I would like to round it up or down to 0.05

Actually, I have found the option in FORMULA > NUMERIC

And I see the same function but I don't see the additional functions as by significance number

Can you assist to me how I can round the number by significance number 0.05

and receive the same result as I received in Excel?

[#2 · Accepted solution]

Hi @Dennisbur,

You're on the right track with the Ceiling() and Floor() functions. Those will always return an integer, so what we can do is modify the input so that an integer return works for us. Specifically, adding a coefficient and a final rescaling so that we apply the ceiling function at the right place in the formula. 

Take your first example, 13.24. Imagine a simple case where you wanted to ceiling the first decimal place, so a result of 13.3.  You could do the following: 

This will return 13.3, because Ceiling() is acting on the value 132.4, and the ceiling of that value is 133. Dividing that by 10 returns us to the base units. 

Another way to write this is using the reciprocal of the units place you want to ceiling, which is the 0.1 place:

So, for your case, where you wish to Ceiling() or Floor() at the 0.05 the value in the :Number column, you need: 

//Ceiling
Ceiling( 0.05 ^ (-1) * :Number ) / 0.05 ^ (-1)

//Floor
Floor( 0.05 ^ (-1) * :Number ) / 0.05 ^ (-1)

These formulas return the answers you are looking for. 

I've attached the table here with those formulas.

I hope this helps!

@julian