You can use number formats, such as the comma style or currency style, or the increase/decrease decimals buttons to control how many decimals Excel displays. These are convenient and can be very useful, but they can also lead to some embarrassing problems in how your numbers display.
For example if you have 1.254 in cell A1 and 1.253 in cell A2, and the total below that, you would see 2.507 as the total as long you are you are using General formatting or displaying at least 3 decimals. But if you are using a number format that displays only two decimals, both cells will display 1.25 and the total will show 2.51. Obviously 1.25+1.25 should equal, 2.50, not 2.51.
The problem comes from the fact that Excel is rounding the numbers for display purposes, but the formula for the total is adding up the actual cell contents, and then rounding the result for display.
The way around this is to use the ROUND() function. It is very simple and works like this: =ROUND(what you want to round, number of decimals). You could enter a formula the formula =ROUND(1.254,2) and the result would be 1.25. Of course nobody would do that because it would be easier to just type 1.25 to start with. In reality you would either want to round a number stored somewhere else, or round the result of a Formula. For example:
If you wanted to use the number from cell A2 and have it rounded to two decimal places, you could enter =ROUND(A2,2).
Or, if you want to divide cell B2 by cell C2, and round the result to 3 decimals, you would use =ROUND(B2/C2,3).
Now let’s go back to the original example. If we used this formula to round the sum of cells A1 through A2 =ROUND(SUM(A1:A2),2)end up with 2.51 for your total, just like in the original problem. You could make another column and place for formulas such as =ROUND(A2,2) in each cell, and then sum those numbers. But you may need to sum them in their original location. There is a way to do this.
If enter type in the formula =SUM(ROUND(A1:A2,2)) and then, instead of pressing Enter, press Ctrl+Shift+Enter, Excel changes yous formula into an array formula. In the formula bar you will notice that it has been changed to {=SUM(ROUND(A1:A2,2))}. The significance of the array formula is that now Excel will round each number it finds in the range A1:A2 and then sum them.
