214 Chapter 5 Spreadsheets
formatted to give dollars and cents instead of showing values rounded to whole dollars. The
end result should look similar to this:1 2 3 4 5 6
A B C D
Employee Hours Rate
Adam 20 $12.75
Betty 28 $11.85
Carole 36 $13.95
Dario $12.50Gross Pay
$255.00
$331.80
$502.20
27.5 $343.75
Totals 111.5 N/A $1,432.75Once you have a spreadsheet set up for a particular application, you can reuse it over and
over again, as the next example illustrates.Example 5.1.2 In the next week, Adam worked 22.5 hours, Betty worked 25, Carole
put in 32, and Dario worked 35. Find the total gross payroll for the company for that
week.To fi nd this, all we need to do is change the hours in the spreadsheet we created in
Example 5.1.1. The program then automatically calculates the desired values.1 2 3 4 5 6
A B C D
Employee Hours Rate
Adam 22.5 $12.75
Betty 25 $11.85
Carole 32 $13.95
Dario $12.50Gross Pay
$286.88
$296.25
$446.40
35 $437.50
Totals 114.5 N/A $1,467.03The total gross payroll for the company this week is $1,467.03.Rounding in Spreadsheets
In this example, the formatting we used hid something from us. If Adam worked 22.5 hours
and made $12.75 per hour, his total gross pay would be (22.5)($12.75) $286.875. Of
course, we know enough to round this to $286.88, and that is the value that was displayed
as Adam’s gross pay on the spreadsheet.
Unfortunately, the spreadsheet displays $286.88, but that is not actually what the pro-
gram “thinks” Adam’s gross pay for the week should be. While you and I know that money
must be rounded to two decimal places, the spreadsheet doesn’t. It displays the value to
two decimal places because that is what we told it to. However, if this value is used in any
further calculations, the spreadsheet will use 286.875, not 286.88. Now, the discrepancies
that this may cause are not large, but they can be aggravating. Further, Adam’s actual gross
pay really will be rounded, and so it actually is not correct for the program to think his
gross pay is 286.875.
We can, and should, address this by including an instruction in the formula to tell the
program that it actually should round to two decimal places—not just in the display,
but in the actual numerical value as well. The instruction to do this in Excel may look
a bit strange at fi rst. To indicate that something should be rounded, you use the formula
“ROUND(number, number of decimal places)”. So in the case of Adam’s pay, the formula
in that cell should be “ROUND(B2*C2,2)”. This formula should likewise be copied into
the gross pay column for each of the other employees.
Do we need to alter the total formula in D6? Actually, we don’t. Since each employee’s
gross pay is now rounded to dollars and cents, there is no way that the total of all of the