IF AND MIN

[D Willett]

=IF(AND(C3<=40),B3,IF(AND(AD3<=AH$13),B3,IF(AND(C3>40,C3<=49),B3*AH$14,IF(AND(C3>=50),B3*AH$15,IF(MIN(C3,I3,O3,U3)<40,B3*AH$14)))))

Guys

I think my formula is tripping over itself, it should return the following:

=IF(AND(C3<=40),B3,
IF(AND(AD3<=AH$13),B3,
IF(AND(C3>40,C3<=49),B3*AH$14,
IF(AND(C3>=50),B3*AH$15,

I know it works up to this point as I added the following but doesn't change the value and just returns B3.

IF(MIN(C3,I3,O3,U3)<40,B3*AH$14)))))

If C3,I3,O3,U3 (any of the cells) <40 multiply B3 by AH15

Where have I gone wrong please.

[HansV]

I'll look at it after dinner if nobody else replies.

[D Willett]

Thanks Hans
I've attached a small snippet of the worksheet.
Here are the conditions if it helps, I've been battling with it all week so probably better if you can see it for yourself.

It is a salary calculator.
Rate is standard and se to £10 as an example.
If C3 <= 40 then there is no overtime.
If AD3 <= AH13 then no overtime.
If C3>40 and <=49 then give overtime at AH14 1.25 * Rate B3.
If C3 >= 50 the give overtime at 1.5 at AH15 1.5 * Rate B3.
Here's the caveat.
If Any of the cells C3,I3,O3,U3 are < 40 then all overtime is AH14 1.25 * Rate B3

This is to address a staff member booking 50 or over for 3 of the weeks and then less than 40 for 1 or more of the other weeks.

I hope this helps.

Temp.xlsx

[HansV]

Hi Dave,

If an employee works 45, 45, 45 and 15 hours, for a total of 150 hours, does he/she get overtime pay for the first 3 weeks because they worked more than 40 hours, or do they get no overtime pay because the total is less than 160 hours?

And I don't understand "If Any of the cells C3,I3,O3,U3 are < 40 then all overtime is AH14 1.25 * Rate B3". Does that mean that the overtime rate 1.5 in AH15 only applies if ALL four weeks are above 40?

[D Willett]

An employee may work 45, 50, 45 which is 20 hrs overtime based on a 40 hr week, he then might make a decision to do a short week on week 4 because he has worked 140 hrs and only do 20 hrs, totalling 160. But he won't get paid the overtime because his monthly attendance should be 160 hrs @ 40 per week. If he does 40, 40, 48, 40, we will pay him 8 hrs overtime @ 1.25 (Time and a quarter). If he does 40, 50, 48, 40, we will pay him 10 hrs @ 1.5 (Time and a half) and 8 hrs @ 1.25 (Time and a Quarter). But if he does 40, 50, 48 and a short week of 30 (because he thinks he's found a loophole) then all overtime reverts to 1.25 (Time and a Quarter).
Our rules are, work 40 hrs per week we will pay you standard 'Rate'. Work up to 50 hrs for that week we will pay overtime at 1.25, work 50 hrs or over in that week we will pay you 1.5 of rate. But take the proverbial and expect overtime where your monthly total is less than 160 then no overtime. So the formula on C3,I3,O3,U3 looks at all the weeks and says, if you have a short week but you worked 50,50,50 & 15, we will pay your overtime at 1.25 because you were absent on week 4 for 25 hrs. We have a clever bunch here...

[HansV]

This is what I came up with, Dave.

Temp.xlsx

[D Willett]

At the minute I'm saying WOW because I can see the extra columns you added (all good) and I probably get you understood the brief because you didn't ask any other questions..... check back with you tomorrow when I've reviewed your guestimate ... mate

[D Willett]

Just testing Hans.
50,40,40,40 should return a basic 160 hrs and 10 hrs overtime. As the employee has done 10 hrs overtime and acheived over 40 each week he is allowed the 10 hrs at time and a half (1.5) £150, the total should be £1750 not £1725.

50,50,50,50 should return 160 hrs and 40 hrs overtime. As the employee has acheived 10 per week he should receive 1600 plus 40hrs at 1.5 = 600 (2200 in total). Hitting 50 or over the overtime should be * 1.5.

50,50,50,10 should return 160 at standard rate, he has worked well over a 3 week period but failed to hit 40 on the last week so his overtime is wiped out to standard rate.

50,50,45,40 should return:

50: 40 @ 10 & 10 @ 15
50: 40 @ 10 & 10 @ 15
45: 40 @ 10 & 5 @ 12.50
40: 40 @ 10 & 0 overtime
Total: £1625.00

Other examples:

ScreenHunter_244.jpg

ScreenHunter_243.jpg

[HansV]

A new attempt.

Temp.xlsx

[D Willett]

Just working in Employee1

(Test 1)
Add 50 to C3 = 10hrs overtime = £150
Add 30 to I3 - C3 correctly reduces to £125
Add 40 to O3
Add 40 to U3
So we have a total of 160.
As these are the basic minimum hrs for 4 weekly pay (40 per week (160)), AD3 should read 0 and AE3 should be £0.
The employee has worked under 40hrs in week 2 therefore all his overtime over 160 should be Rate * 1.25. In this case his total worked hrs meet the minimum requirement of 160 so he has no overtime.

(Test 2)
Add 50 to C3 = 10hrs overtime = £150
Add 55 to I3 - L3 returns 10, this should show 15 (55 - 40) M3 should be £225
Add 40 to O3
Add 40 to U3
So we have a total of 185 (correct in AA3) but AD3 should be 25 and AE3 should be £375, AF3 should be £1975.
The employee has met the minimum requirement of 40hrs per week and 160hrs, he has worked 50 in week 1 and 55 in week 2, he is entiled to the 25 hrs at 1.5 time and a half.

Hope this makes sense Hans.

[HansV]

I had interpreted the 160 rule slightly differently; that was easily corrected.
And I had forgotten to copy the formulas from column F to columns L, R and X.
Try this version. Should AF3 be £1500 or £1600?

Temp.xlsx

[D Willett]

AD3 & AF3 should be £1600 as minimum 4 week hrs acheived even though overtime and 30 all reach 160.

Yes I wondered about L R and X ,0)10)

Cheers

[HansV]

It's amazingly complicated! One more try:

Temp.xlsx

[D Willett]

I know Hans. Its a new bonus system devised by our MD, muggins here always get the task to solve.

Thats looking to be the answer to the Krypton factor Hans

[D Willett]

Hi Hans

I've had to tweak a couple of things and just stuck on one formula.
With the attached.

If the Overtime Hrs are in a minus, then can the Overtime Total return a zero, other than that the current formula works.

[HansV]

Change the formula in AI3 to

=IF(MIN(D3,K3,R3,Y3)<AN4,MAX(AF3-AN5,0),G3+N3+U3+AB3)

[D Willett]

Perfect, Thank You.