Rounding to Powers of 10

If you want to round a number to the nearest 10, 100, or 1000, you can use the ROUND function with negative values for the second parameter. This approach works very well—and very quickly.

For instance, if you have a value in cell D6 and you want to round it to the nearest 100, you could use the following formula:

=ROUND(D6,-2)

This rounds to second digit to the left of the current decimal place. For instance, if D6 contains the value 12345, then the formula returns 12300. Similarly, if D6 contains 12351, then the formula returns 12400.

You can use the same technique to round to the nearest 10 by using -1 as the second parameter, or to the nearest 1000 by using -3.