Nov 07 2023 01:10 PM
Nov 07 2023 01:10 PM
I am having a problem with fraction calculations using the sum function.
Cells a1 and b1 formatted as time values
Cells c1, d1, e1 in number format
hourly rate entered into 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
Nov 07 2023 01:21 PM - edited Nov 07 2023 01:21 PM
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.
Nov 07 2023 02:07 PM
Nov 07 2023 02:33 PM
Nov 07 2023 03:04 PM
Nov 07 2023 07:47 PM - edited Nov 07 2023 08:03 PM
@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.
Nov 07 2023 11:19 PM - edited Nov 07 2023 11:59 PM
@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.