» 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