Rounding Numbers

December 06, 2001 - by Bill Jelen

Last night, I delivered a presentation on Advanced Excel Tips and Tricks to the Akron & Canton chapters of the Institute of Manageral Accountants. This was a great group of very fluent Excel users. Here is some of the Q&A from that session.

If I enter a formula to take one-third of a number like 100,000 - how can I round that result to get just the whole number portion, or even just the thousands? I know I can format it to hide the decimals, but I don't want the decimals to enter into subsequent calculations?

Surround the formula with the =ROUND() function. If the formula is currently A2/3, then use this formula to round to the nearest whole number:

=ROUND(A2/3,0)

The ",0" in the formula says that you want to round to have no digits to the right of the decimal place. If you wanted to have 2 decimal places, then use

=ROUND(A2/3,2)

To round to the nearest thousand, indicate a negative number as the 2nd argument for the round function =ROUND(A2/3,-3) will round to the nearest 1000.