Why things don’t always add up in Excel

Number Formatting vs Rounding in ExcelNumber formatting is a great way to change the appearance of a number, including the number of decimal places of accuracy, but it can also yield some strange results. If you’ve ever had the experience of puzzling over a column of numbers that AutoSum can’t seem to add up, or perhaps you’ve got percentages of a whole that add up to more than 100%, then this article is for you!

Number Formatting

Number Formatting is a quick and easy way to change the appearance of a number. Many people use it to convert calculated multiple-decimal place numbers into easy-to-read currency values. Number Formatting also allows you to determine how many decimal places of accuracy to show.

It is critical to remember, however, that you never lose the actual number when you use Number Formatting to hide decimal places. So, for example, $49.49 + $51.49 will always equal $100.98, even when you change the Number Formatting to show the numbers as $49 + $51.

The Round() Function

If you are looking for accuracy in your calculations and you want to “throw away” the precision offered by extra decimal places, then you want to use the Round() function. The Round() function takes two arguments, the number to be rounded, and the number of decimal places to be shown.

You can enter the Round() function via the Function Insertion button, the Math & Trig dropdown in the Function Library group of the Formulas tab, or by just typing it.

Remember these two tips when using the Round() functions:

  • It is always better to run the function on a cell address rather than on a number.
  • You can’t run the Round () function on the source cell.