Rounding full numbers (percentage) to nearest 10
I am trying to find a formula to round in full numbers (not decimal places). I have a column that creates a percentage of "investment paid", and I want to round this to the nearest 10. Ie; if the percentage result in the column is 59%, I would like this to display as 60%. And conversely, if the percentage result is 61%, I want this to display as 60%.
(This is due to slight discrepancies in actual payments received due to bank fees and other adjustments, yet I need the percentage figure to be a round number to the nearest 10 for further use in additional calculations elsewhere in the sheet).
My current formula for working out the percentage is: =SUM([Paid to Date]98 / [Committed Capital]98)
Can you please help.
Cheers
Catherine
Comments

Try using the ROUND function and in the portion for number of decimal places, use 1. This should work for you.

Paul,
I tried that earlier today, and it didn't seem to work with percentages. I haven't had time to delve deeper yet.
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Ah. That makes sense since percentages are actually decimals instead of whole numbers. I haven't had much time to do any testing either, but do you think this will work? It sounds like it should...
What about multiplying the result by 100 to get a whole number, round THAT, then divide by 100 to get it back into decimal form for a percentage?
=ROUND(original formula * 100, 1) / 100

Nicely done!
Yes, that should work I think!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Awesome!!
Catherine:
Let us know if this works for you...

That worked perfectly, thanks Paul!!

Help Article Resources
Categories
Check out the Formula Handbook template!