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.

rounding01

Or, you could make things even more confusing, and show that 2+3=4.

rounding03

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.

rounding02

In the second example, the actual numbers are 1.54 and 2.54, which add up to 4.08.

rounding04

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.

 

7 Ways to Round in Excel http://blog.contextures.com/

_____________

Save

Save

You may also like...

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.

  2. Lynda says:

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

Leave a Reply to Lynda Cancel reply

Your email address will not be published. Required fields are marked *