It is 4 PM on Friday. You are already in weekend mode when you boss calls.
He is going to be working on the budget over the weekend and he wants you to give him some rations. He is technologically impaired, so you create the quick spreadsheet that appears in the next figure and email it to him.
The phone rings at 4:15 PM. Your boss likes the quick turn around, but would like you to make Col C only four decimal places. You highlight cells C5:C9 and change the number format to four decimal places, save the changes and email the revised spreadsheet (as shown in the next figure) to your boss.
You glance at your watch. It is 4:30 PM. You have made it through the end of the week. The phone rings. You grimace when you hear the boss’ voice. First he asks you if you know basic addition. Then he questions whether you should begin cleaning out your desk . Apparently the numbers in Col C do not add to the total.
What happened?
The answer is that many Excel users mistakenly believe that changing the format of a number instructs Excel to round a number. They do not know or understand that when Excel uses a cell in a formula it uses the calculated value of the cell, not its displayed value. In this example the SUM(C4:C6) function is adding all of the decimals in those cells whether they are displayed or not.
To correct this problem you need to place your formula within the ROUND function. The syntax for this function is ROUND(x,n) where x is the cell address, number, or formula that you wish to round, and n is the number of decimal places to round to. This number should be the same as the number of digits being used for your cell formatting. The next figure shows the corrected spreadsheet with the formulas being rounded to four decimal places. The amounts now total to the displayed sum and you have kept your job.
You should be aware that Excel (at least going back to the 2003 version) has three different round functions: ROUND, ROUNDUP, and ROUNDDOWN. ROUND uses a mathematical approach to rounding: anything five or greater gets rounded to the next larger number, while anything four or less gets rounded to the next smaller
number. As the names of the other two functions imply, ROUNDUP always rounds to the next larger number while ROUNDDOWN always rounds down to the next smaller number.
This figure shows the results of the three functions for both positive and negative numbers.
