Rounding full numbers (percentage) to nearest 10

Catherine McLennan

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

Paul Newcome

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

Andrée Starå

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

Paul Newcome

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

Andrée Starå

Nicely done!

Yes, that should work I think!

Paul Newcome

Awesome!!

 

Catherine:

 

Let us know if this works for you...

Catherine McLennan

That worked perfectly, thanks Paul!!

Paul Newcome

Happy to help!