Summing Rounded Numbers

How Excel performs calculations:

Excel does not take number format into account when performing mathematical calculations. When performing a calculation, Excel operates on the entire number, using up to 15 significant digits, without considering the formatting.

Problem:

When the numbers displayed by the cells differ from the numbers Excel uses for calculations, there can be an apparent difference in the totals of those cells.
For example, in cells B1 to B5 in the screenshot, the numbers were rounded based on their number formats. Notice that the total in cell B6 is not equal to the total of the displayed numbers, which is 16 (see explanations to the Array Formula in C6 in the screenshot at the last page of this Tip).

Solution 1:

Deleting Number (Permanent – No Undo!)
1. From the Tools menu, select Options.
2. Select the Calculation tab, and then select the Precision as displayed option button.
All places after the decimal point are deleted in the cells. The numbers displayed in the cells are whole numbers, and the total is the sum of these whole numbers.

Disadvantage: There is no way to undo this operation, as the numbers have been permanently deleted.

Solution 2:

Use an Array Formula to Total Rounded Numbers
To total numbers rounded to the nearest integer:
1. In cell C6, type =ROUND (see the screenshot in the first page of this Tip).
2. Press Ctrl+A for the Function Arguments dialog box, and in the first Number argument box of the ROUND formula, enter the range C1:C3.
3. In the Num_digits argument box, type 0 to round the number to the nearest whole digit and click OK.
4. After the equals (=) sign, type SUM.
5. Type ( to open the parentheses, press End, and then type ) to close the parentheses.
6. Press Ctrl+Shift+Enter to create an Array Formula (if the formula returns a #VALUE error, press F2, and then press Ctrl+Shift+Enter).

To total numbers rounded to thousands (see the screenshot at the bottom):

Follow the steps above and insert an Array Formula into cell B5, with -3 in the ROUND formula's second argument box.
Screenshot // Summing Rounded Numbers
Summing Rounded Numbers

Users are saying about us...

  1. I've always liked this tip, but wish there was an inverse version, so you could format percentages as if they were whole numbers. Anyone got a solution for that? Now I just use a formula in the sheet to multiply each cell by 100.

  2. "If I understand correctly, you have a number in a cell such as 0.20 which you would like to display as 20%.

    Currently you are multiplying it by 100 to change the value to 20?

    Rather than doing that, you can use a number format to display the value 0.20 as 20% by using the following:

    0%

    If you wanted one decimal place (0.2234 -> 22.3%), then use:

    0.0%

    Hope that helps, "

Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube