PDA

View Full Version : Excel Calc problem,



John W
20-07-2002, 09:40 AM
Id like to be able to calculate a persons Gross pay, when they work overtime. The overtime rule is Time and a half for the first 3 hrs, double time thereafter but only after they have worked more than 40 hrs.
In Cell A1 = Hours worked in week
In Cell A2 = Hourly Rate
In Cell A3 = the above calculation.

I can work out for uptp 43hrs but cant get my head around additional =If statements.

Thanks......John

cadifan
20-07-2002, 09:55 AM
Cell A1 = Hours Worked Ordinary Time
Cell B1 = Hours Worked Overtime x 1.5
Cell C1 = Hours Worked Overtime x 2
Cell A2 = Ordinary Time Hourly Rate
Cell B2 = Rate x 1.5
Cell C2 = Rate x 2
Cell A3 = Total Ordinary Time
Cell B3 = Total Time and a half
Cell C3 = Total Double time
Cell D3 = Total Gross Pay for the week

There may be another way but this will work.

wotz
20-07-2002, 11:25 AM
try this for the calculation line
=IF(B1>40,IF(B1>43,40*B2+3*B2*1.5+((B1-43)*B2*2),40*B2+((B1-40)*B2*1.5)),B1*B2)

It assumes hours are in B1 and rate is in B2

lloyd
20-07-2002, 01:14 PM
Hi John, this works

=IF(A1<=40, A1*A2, IF(A1<=43, (40*A2)+((A1-40)*(A2*1.5)), (40*A2)+(3*(A2*1.5))+((A1-43)*(A2*2))))

Cheers, Lloyd

lloyd
20-07-2002, 01:22 PM
Although it can be calculated with an if statement, something along the lines of what cadifan sugested might be better because it is very transparent and easy to see how the end figure was arrived at.

John W
20-07-2002, 02:11 PM
Thanks to those who replied.

Ill stick with the formula in one cell, although the others would have worked as well.

Im transplanting the One Cell formula into an existing Paysheet, much more work to go the other option.

Thanks again.....John.

-=JM=-
20-07-2002, 08:05 PM
You could also use a VLOOKUP for part of it.