Forum Home
Press F1
 
Thread ID: 22384 2002-07-19 21:40:00 Excel Calc problem, John W (523) Press F1
Post ID Timestamp Content User
63595 2002-07-19 21:40:00 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 		John W (523)
63596 2002-07-19 21:55:00 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. 		cadifan (286)
63597 2002-07-19 23:25:00 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 		wotz (335)
63598 2002-07-20 01:14:00 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 (1076)
63599 2002-07-20 01:22:00 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. lloyd (1076)
63600 2002-07-20 02:11:00 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. 		John W (523)
63601 2002-07-20 08:05:00 You could also use a VLOOKUP for part of it. -=JM=- (16)
1