Calculation error in spreadsheet

[Phil_Couzens]

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

[#2]

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.

[#3]

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.

[#4]

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

[#5]

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 & b

[#6]

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.

[#7]

@Phil_Couzens  wrote:  ``how do I rectify the rounding error to get``....

.... the same result when you explicitly round the numbers to 2 decimal places on the calculator.

Well, you explicitly round the calculated numbers to 2 decimal places in Excel.

The formula in C1 should be =ROUND((B1-A1)*24, 2)

And the formula in E1 should be =ROUND(C1*D1, 2)

Aside....  There is no "right" or "wrong" here.  It is simply a question of the result that you want.  There are many situations where we want a WYSIWYG result.  Paper receipts and government forms, for example.

[#8]

I did all that and got the right result.
However, when you enter a figure in d1 it shows a zero

[#9]

@Phil_Couzens  wrote:  ``I did all that and got the right result.  However, when you enter a figure in d1 it shows a zero``

Do you mean:  with 22 in D1 initially (as you wrote originally), the two modified formulas in C1 and E1 display 2.67 and 58.74, as intended.  But after that, when you enter a different number into D1, E1 displays zero?

I cannot know why that might happen because you do not provide sufficient information.  Obviously, it has nothing to do with the modifications in C1 and E1, if they worked initially.

Attach an Excel file that demonstrates the __new__ problem:  E1 displaying zero.

[#10]

When I re-opened the file e1 was displaying the 22 value ok.
Thanks for help