Forum Discussion
Calculation returns wrong value
Simple calculation but returns wrong value.
=H33*G34
H33 is overtime hours, formatted as number, 2 decimals (.18)
G34 is payrate, formatted as Accounting, 2 decimals ($21)
The ending cell is formatted for accounting. I have also tried to format it as number, but the incorrect value does not change.
The Excel calculation returns a value of $3.85. The correct calculation should be $3.78.
I have tried adding a ROUND function, changing the Formula Option to "Manual", enabled "Iterative Calculations", and used Options>Advanced>When calculating this workbook>Set precision as displayed which really messed up the return value.
What am I missing?
- I suspect H33 is not 0.18, but 0.18333333 (11 minutes). Regardless of how you format the cell, it doesn't change the actual value of the cell contents.
Try: =Round(H33,2)*G34
8 Replies
- I suspect H33 is not 0.18, but 0.18333333 (11 minutes). Regardless of how you format the cell, it doesn't change the actual value of the cell contents.
Try: =Round(H33,2)*G34- Thanks....this worked. I had to copy the "value" of H33 (it was built on evaluating other cells) into the next cell and round that. Very frustrating to understand why, and make me lose trust in Excel calculations....
[.... withdrawn to avoid confusion ....]
Yes, I am assuming the OP knows whether or not the desired result follows company policies and applicable federal/state laws. I'm not a legal expert, but from what I've read regarding the DOL policies on rounding, they look for rounding practices to be consistent and neutral. For example, if an employee clocks in at 8:08, you may round up to 8:15, but to be fair you must round down if they clock in at 8:07. It's a clear violation if you always rounded down (i.e. not neutral). So, over time, a consistent rounding practice should average out and the employee would end up being paid for all time actually worked (from what I've read, the DOL has indicated it would accept rounding up to 30 minutes as long as the rounding averages out, so rounding to 2 decimals appears reasonable).
None of which is a legal opinion, of course, I'm just saying I don't see any obvious issue with it.
