Quote:
Originally Posted by riothecat
the 2 numbers in red are the different ones. The red number to the right of net check amount is correct the red number below gross wages is incorrect.
|
I looked at the file. What I see is....
Both I2 ("gross wage") and L1 ("check amount")
appear to be the same, namely $368.46.
Their
actual values differ from the displayed values. In particular, I2 is $368.45
75, and L1 is $368.45
8333333333.
That difference can and should be fixed with explicit rounding. The formulas should be:
I2: =
ROUND(((H2-40)*9.25*1.5)+(40*9.25)+0.77
, 2)
L1: =
ROUND(H2*9.25
, 2)
where H2 is $39.8333333333333.
However, it is unclear why the displayed values should be the same. And perhaps that is your point, to some degree.
In fact, we can demonstrate algebraically that I2-L1 should be (H2-40)*9.25*0.5 + 0.77. And they do indeed differ by about that much.
But more to the point: I believe that
both formulas are incorrect, if your intent is to incorporate 1.5x overtime pay into gross wages in I2.
(Certainly, the formula in L1 does not do that. That formula seems irrelevant.)
I am only familiar with Calif overtime rules. But I suspect they are similar in most states. Based on Calif rules, the calculation in I2 should be:
Code:
=ROUND(SUMPRODUCT((G2:G8>8)*(G2:G8-8))*9.25*1.5
+ MAX(0, SUMPRODUCT((G2:G8<=8)*G2:G8 + (G2:G8>8)*8) - 40)*9.25*1.5
+ SUMPRODUCT((G2:G8<=8)*G2:G8 + (G2:G8>8)*8)*9.25, 2)
where G2:G8 are the daily hours in a work week, 9.25 is the hourly rate, and 1.5 is the overtime factor.
SUMPRODUCT((G2:G8>8)*(G2:G8-8)) is the sum of the daily overtime hours in excess of 8 hours per day for the week.
SUMPRODUCT((G2:G8<=8)*G2:G8 + (G2:G8>8)*8) is the sum of the daily regular hours for the week, up to 8 hours per day. (The daily excess is already accounted for in the previous paragraph.)
Thus, MAX(0, SUMPRODUCT((G2:G8<=8)*G2:G8 + (G2:G8>8)*8) - 40) is the overtime hours in any excess of 40 regular hours for the week. (Zero if there are fewer than 40 regular hours.)
With that formula, the actual and displayed gross wages in I2 is $376.71.
Note: Alternatively, an employer might choose to round each component calculation separately. In that case, gross wages in I2 would be $376.7
0.
Does that help? Or did I misunderstand your intentions or how overtime is calculated for your state?
If the latter, please specify your state and the overtime calculations as you understand them.
-----
There are other rounding issues that I believe you should address.
The formulas in I4:I8 should be explicitly rounded. For example, in I4:
Code:
=ROUND(VLOOKUP(I2,R2:V4,3)
+(VLOOKUP(I2,R2:V4,4)*(I2-77.9-VLOOKUP(I2,R2:V4,1))), 2)
A formula in I9 should also be explicitly rounded. But since it is a constant in your example, no additional rounding is required (and it would be redundant).
Without explicit rounding, the actual original values (based on the incorrect overtime calculation in I2) are:
I4: $27.983625
I5: $5.96553045
I6: $22.844365
I7: $5.34263375
I8: $3.684575
On the other hand, I believe it is correct
not to explicitly round the decimal hour calculations in G2:G9.
That choice demonstrates why setting the "Precision As Displayed" option would be incorrect, since you choose to display decimal hours with only 2 decimal places.