# Excel Query

TheTerminator 00:24 19 Jan 2003
Locked
TheTerminator 00:24 19 Jan 2003

In short I am totally stuck!

I am required to produce a spreadsheet that works out the pay of workers,
where upto 35 hours a week is paid at normal salary,
36 - 40 hours a week is paid at time and a half and where
41+ hours a week is paid at double time.

Can this be sorted out with just the information given as the hours worked?

I'm sure that there is a function in excel that can work this out but i am baffled.

For example, if a person works 39 hours, they will get paid 1 - 35 hours at normal salary and 4 hours at 1.5 salary, and at £5 an hour would receive £205 (i think). And I would like this to be done in one formula! Is this possible?

I would like this to be done automatically!

Any help (Gandalf?) would be received very gratefully.

barryoneoff 01:46 19 Jan 2003

Havent used excel for ages but something in the formula on the lines of....If (hours >=41 blah blah,If hours >36 blah blah....

Cant reall work it out in my head, will try to find a way in excel. If you dont get a solution will be back later (much later!)

Cheers

jazzypop 03:46 19 Jan 2003

You have three salary bands, so use three columns, plus one for the total hours, and one for the final amount.

Col 1 = total hrs worked

Col 2 = 35 (assuming that everyone always works at least 35 hrs)

Col 3 = ((Col 2 - 35) x 1.5))

(total hrs @ time + 1/2). You will need to round this down to a maximum value of 6 with an 'if greater than 6, 6, else value' type of statement

Col 4 = ((Col 2 - 41) x 2)) (Total hrs greater than 41 at double time)

Col 5 = (Col 2 + Col 3 + Col 4) x hourly rate

This is very simplistic, and I have no doubt at all that the likes of VOG and Cherria will come up with far more elegant principles. However, especially when it comes to calculating pay, it always seems sensible to me to make each step transparent (and therefore easily checked) rather than forcing everything into one formula. It makes things a lot easier when it comes to making the inevitable adjustments (Bank Holidays, special bonuses, deductions etc).

The columns referred to above can be placed off the visible area to the right for neatness, and a single cell adjacent to the worker's name can have an '=Col 5' formula to keep things looking neat.

Once you have these formulas set up in the right columns, select them and drag them down as many rows as you have workers. You can add a Totals column at the bottom to automatically see the total wage bill.

Not at all elegant, but simple to implement and for anyone else to understand if, for example, you are on holiday or taken ill.

VoG™ 09:35 19 Jan 2003

With hours worked in A1 and hourly rate in B1, in C1 enter

=IF(A1>=41,2*B1*(A1-40)+5*1.5*B1+35*B1,IF(A1>=36,1.5*B1*(A1-35)+35*B1,A1*B1))

This formula can be copied down.

The formula could be simplified but I've left it in longhand so you can see what's going on, hopefully.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?