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.

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.

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.