Calculation error in spreadsheet

Question

I am having a problem with fraction calculations using the sum function.

For Example:

Cells a1 and b1 formatted as time values

Cells c1, d1, e1 in number format

c1 sum(b1-a1)*24

hourly rate entered into D1

e1 sum(c1*d1)

e.g. 04:40 - 02:00 = 2.67hrs

e.g. D1 £22

Result in e1 = 58.67

However, when done on a calculator the result come out at £58.74

When using full hrs result in e1 comes out correct

Anyone suggest a way of overcoming this

mibrady · Answer

Hi Phil! Can you share what you entered in the calculator? From what I see, they worked 2 2/3 hours (8/3) times 22 as the rate. When I put that in a calculator I get 58.67 (rounded). I thought it might be a rounding error but I can't replicate your 58.74.

mibrady · Answer

Ah!  It just occurred to me.  It is a rounding error.  So, I realize that 58.74 is 2.67*22.  However, 2.67 is a rounded number for 2 2/3.  Thus, 58.67 is actually the more accurate number as it doesn't round until the end of the equation.

phil_couzens · Answer

So how do I rectify the rounding error to get the correct result

phil_couzens · Answer

If I enter the 2.67 in an extra cell manually, and then formulate the final cell. The result is 58.74.Could it be a format error in cells a &amp; b

mibrady · Answer

So, the actual solution to 4:40-2:00 in hours as a number is 2.66666666..... not 2.67.  That is why I was referencing 2 and 2/3 as that is the more exact representation.  40 minutes is 2/3 of an hour. So, when it multiplies 22 by the actual formula, it multiplies it by the unrounded number.  That results in the 58.67.  I would argue that 58.67 is the correct answer, not 58.74.

Forum Discussion

Calculation error in spreadsheet

Share

Resources