sum function

  • Thread starter Thread starter Lori Null
  • Start date Start date
L

Lori Null

I have a time sheet that calculates the overtime pay rate
for each employee. When I use the auot sum to add all the
calculated pay rates it does not round correctly. I am
off a few pennies each time. Is there anything I can do
differently that will correct this.
EX.
Joe 2.0 $14.35
Mary 1.0 $10.51
Total $24.86
 
Either choose Tools/Options/Calculation and check the Precision as
displayed checkbox (which is a global setting), or calculate your
pay rates using ROUND():

=ROUND(B1 * C1, 2)

Note: as long as you're only doing multiplication and addition,
ROUND() should work well. Subtraction and division can lead to
another error:

A1: 10.00
A2: =ROUND(A1/3, 2) ==> 3.33
A3: =ROUND(A1/3, 2) ==> 3.33
A4: =ROUND(A1/3, 2) ==> 3.33
A5: =SUM(A2:A4) ==> 9.99

To correct this you can use something like:

A4: =A1-SUM(A2:A3) ==> 3.34
A5: =SUM(A2:A4) ==> 10.00

but for a large column of numbers, the error can be significant.
 
Back
Top