7 Ways to Round in Excel

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.

Hidden Information

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.

_____________

Save

Save

6 Responses

1. Patrick Matthews says:

And just to add to the fun, the Excel ROUND() function uses a different technique than the VBA Round() function, with Excel’s built-in function always rounding away from zero when the next digit is a 5, and VBA using the “banker’s rounding”. That’s been known to confuse VBA beginners.

• Thanks Patrick! I don’t remember ever using the Round function in VBA, so that’s good to know.

• Patrick Matthews says:

The first time I encountered that I think I lost almost an entire day figuring out why my code wasn’t working :) To make VBA use Excel’s built-in function:

MyVariable = Application.Round(InputVariable, 2)

2. Lynda says:

2+2=5, for very large values of 2…