calculating hourly rate

[andrewb90]

Hello all,

I'm having a bit of a problem, and I'm hoping there's a simple solution.

I am trying to enter in Cell B1 an annual pay amount ($28,600), then in C1 place the number of hours worked per week (50), then the next cell D1 should display the hourly pay rate ($10). Iv'e managed to calculate this starting from the hourly rate x the hours per week, but am struggling to reverse it.

For this example:

$10/hr x 50 hours
reg pay $10x40 hours = $400
O/T pay $15x10 hours = $150
Weekly pay = $550
$550 x 52 weeks =$28,600

I just need to be able to enter any yearly total and amount of working hours to calculate the pay rate that would be equivilent based on 1.5x pay for all hours above 40.

Any help would be greatly appreciated.

 

[jtakw]

Hi,

Not sure which formula you needed, hope this covers it:

Excel 2010
	B	C	D	E	F	G	H	I
1	Annual Pay	Hours per Week	Regular Rate	Regular Pay	OT Rate	OT Pay	Total Weekly Pay	Actual Annual Pay
2	$28,600.00	50	$10.00	$400.00	$15.00	$150.00	$550.00	$28,600.00
Sheet1

Cell Formulas
Range	Formula
E2	=MIN(C2,40)*D2
G2	=MAX(0,C2-40)*F2
H2	=E2+G2
I2	=H2*52

 

[Nishant94]

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Annual Pay[/TD]
[TD="align: center"]Hours Worked/week[/TD]
[TD="align: center"]Pay Rate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]28600[/TD]
[TD]50[/TD]
[TD]10 (Formula Goes here)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]28600[/TD]
[TD]60[/TD]
[TD]7.85714287[/TD]
[/TR]
</tbody>[/TABLE]

Formula in C2:

=(A2/52)/IF(B2>=40,(40+((B2-40)*1.5)),B2)

Drag the formula down.

 

[andrewb90]

I'm a bit confused. D2 shows no fomula but that result would need to be calculated based on the values from B2 & C2.

 

[andrewb90]

Thanks Nishant! that was the exact formula that was eluding me

 

[Nishant94]

You'r Welcome.