This article will explain how to round your figures to the next decimal range (10 cents, 25 cents, 50 cents, ...)
Standard use of the ROUND function
In Excel, there are 3 functions to round your number to a specified number of digits.
- ROUND
- ROUND.INF (to round to the lowest decimal places)
- ROUNDUP (to round to the next decimal places)
For example here we have several example of rounding precision using data from column A and precision from row 1. The formula has been build with mixed references to write the formula only once for whole table.
=ROUND($A2;B$1)
Round to tenth, hundredth
But with these functions you can also round by tenth, hundredth or thousandth.
This trick will be the solution 😉
Round by decimal
In the following table, we have the figure we want to round to the next decimal range.
Round up to the next 10 cents
First of all, we need to transform our numbers so that we no longer have decimals. And this is very easily obtained by multiplying our values by 100.
= B2*100
Then, we will round to the next ten using the following formula
=ROUNDUP(B2*100; -1)
And of course, don't forget to divide this result by 100 (the same number as the initial multiplication) to have the result you expect.
=ROUNDUP(B2*100;-1)/100
Round up to the next 25 cents
Here, the principle is exactly the same but here we will multiply by 40. Because 1000/25 = 40
=ROUNDUP(B2*40;-1)/40
Rounded up to the next 50 cents
This time, we will take as a multiplier 1000/50 = 20
=ROUNDUP(B2*20;-1)/20
