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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!