Forum Discussion
Calculation error in spreadsheet
MicrosoftHi 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.
- mibradyAh! 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.
- 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- mibradySo, 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.
- So how do I rectify the rounding error to get the correct result
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.
