rounding up to a specific number
Does anyone know if it is possible to round up to a specific number?
I would like to round up a column of numbers to the nearest 9.95. i.e 1256.18 would round up to 1259.95, 862.68 would round up to 869.95 etc.
I'm really hoping somebody can help.
Best Answer

That would be this:
=[Total]@row+(9.95MOD([Total]@row,10))
or
=Total66 + ( 9.95  MOD(Total66 , 10 ))
But it's best practice to use @row instead of row number when doing formulas.
Answers

Let's do some maths :)
The formula you need should be this one:
=[Number to round column]@row+(9.95MOD([Number to round column]@row,10))
MOD will return the rest of the division of your number by 10, i.e. 6.18 & 2.68 in your example. Which is all you need to round up to 9.95
Hope it helped!

Thank you David.
So just to clarify, if the number I want to round is in column "total" and row 66, could you type out the exact formula?
Thanks
Tracey

That would be this:
=[Total]@row+(9.95MOD([Total]@row,10))
or
=Total66 + ( 9.95  MOD(Total66 , 10 ))
But it's best practice to use @row instead of row number when doing formulas.

@David Joyeuse Nice use of the MOD function!! 👍️👍️

@David Joyeuse thank you so much. The first formula didn't work for me but the second one did, so hours of work saved!!
Help Article Resources
Categories
Check out the Formula Handbook template!