Getting wrong answer when calculating OT pay

[Glen M]

I couldn't find a similar question on the board. I appoligize if this has been asked and answered. I have a sheet we use to calculate OT pay and regular pay. I am trying to convert the OT to straight time by multiplying the OT hours by 1.5 then multiply that by the straight rate. I get an incorrect amount.
here is an example.

The straight rate is $9.25 the OT rate is $13.88

1 x 13.88 = 13.88
2 x 13.88 = 27.76
and so on

Now when I take 8 hours of OT and convert it to 12 hours (8*1.5) and multiply that by 9.25 I get 111.00
but the correct amount of OT is 8*13.88 = 111.04

My question is how can I get excel to give the right answer.

I tried using the Round command but that didn't work.
These are the current formulas in the columns:
in the "Total regulars pay" - =IF(OR([@[HOURS WORKED]]="",[@[HOURS WORKED]]=0),"",[@[HOURLY RATE]]*[@[HOURS WORKED]])
In the "OT Hourly rate" - =IF(OR([@[WORK SITE]]="Washburn",[@[WORK SITE]]="KNI"),16.5,13.88)
In the "Total OT Pay" - =IFERROR(IF([@[OT HOURS]]=0,"",[@[OT HOURLY RATE]]*[@[OT HOURS]]),"")
In the "Total Hours (converted)" - =IF([@[HOURS WORKED]]="","",[@[OT HOURS]]*1.5+[@[HOURS WORKED]])
In the "Total Bill" - =IFERROR(IF(OR([@[OT HOURS]]="",[@[OT HOURS]]=0,[@[HOURS WORKED]]="",[@[HOURS WORKED]]=0),[@[TOTAL REGULAR PAY]],[@[TOTAL OT PAY]]+[@[TOTAL REGULAR PAY]]),"")

Driving me nuts. Thanks for helping.

 

[Fluff]

I am trying to convert the OT to straight time by multiplying the OT hours by 1.5 then multiply that by the straight rate. I get an incorrect amount.

That's because 1.5*9.25 is 13.875 not 13.88

 

[Glen M]

That's because 1.5*9.25 is 13.875 not 13.88

Right and I tried using the Round command and that didn't fix it. Do you know how I can make it right?

 

[Fluff]

Why do you need the converted hours?

 

[Glen M]

Why do you need the converted hours?

I work for the Gov and I have to input this information into an old DOS based system. I don't want to have to enter two different bills. I'm trying to save myself some time and work. In my system I can only create a bill for one rate. If I don't convert it I would have to create a bill for straight time and one for OT for every department.

 

[Fluff]

But if the site is Washburn or KNI it's a different rate & so that won't work if you multiply it by 1.5
Surely the bill will just be the Total pay which you've already calculated.

 

[Glen M]

But if the site is Washburn or KNI it's a different rate & so that won't work if you multiply it by 1.5
Surely the bill will just be the Total pay which you've already calculated.

Correct the bill is for the total amount. If I can convert it I can create one bill for the department. In this case its Nursing. If can't convert it I would have to create two bills for Nursing.

 

[Fluff]

Correct the bill is for the total amount.

But you are already calculating that, so I don't see what the problem is.

 

[Glen M]

But you are already calculating that, so I don't see what the problem is.

When I convert the hours the amount it wrong. 265 hours x 9.25 = 2451.25 but the sheet is saying 2451.29

 

[Fluff]

You've shown a formula for Regular pay & a formula for OT pay, which should both be correct and then you have a formula adding the two together, so I still don't understand why you need to convert the hours. I seems you are just making life more difficult for yourself.

If you really need to convert the hours then use something like 1.50055, but it will never be accurate.