It’s not usually as obvious as the error in the screen shot below, but have you ever seen Excel adding things up incorrectly? No wonder some people take out a calculator, to check the totals! Your trusty old calculator wouldn’t tell you that 2+2=5.
Excel Is Wrong
Yes, it certainly looks wrong, but sometimes 2+2 = 5 in Excel.
Or, you could make things even more confusing, and show that 2+3=4.
You might be able to guess why those totals look wrong. Even a simple spreadsheet like this one can have things going on below the surface.
The problem with “incorrect” totals can occur if you use number formatting to make the numbers look rounded. Excel hides the decimals, but they’re still stored as part of the number.
In the first example, the numbers look like 2+2, but the actual numbers are 2.3 and 2.3, which add up to 4.6.
In the second example, the actual numbers are 1.54 and 2.54, which add up to 4.08.
So, be careful if you’re using number formatting that hides the decimals, or someone might question the accuracy of your spreadsheet!
Use Excel Functions for Rounding
In some cases, you might want to use a function to round the numbers, instead of just hiding the decimals.
To help you get started, I’ve made this short slide show that shows 7 ways to round in Excel.
It shows number formatting, and the problem it can cause.
There are also rounding examples that use the Excel functions ROUND, ROUNDDOWN, ROUNDUP, FLOOR, and CEILING.
7 Ways to Round in Excel
To see the details for the rounding examples in the slide show, go to the Excel Rounding Functions page on my Contextures website. There are workbooks to download too, so you can follow the examples.
It also has an MROUND example, and looks at the variations for the FLOOR and CEILING functions.