Rounding full numbers (percentage) to nearest 10

Options
edited 12/09/19

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)

Cheers

Catherine

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

Awesome!!

Catherine:

Let us know if this works for you...

• Options

That worked perfectly, thanks Paul!!

• ✭✭✭✭✭✭
Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!