advanced 'if' multiply

[spezticle]

variables:
A = base pay rate
B = overtime pay rate
x1 = hours worked in pay period 1
x2 = hours worked in pay period 2
y = money earned
z1 = hours week 1 < 40
z2 = hours week 2 < 40
z3 = hours week 1 + week 2 < 80

if x1 < 40 then y = x * A
if x1 > 40 then y = (40 * A) + (z * B)
if x2 < 40 then y = x * A
if x2 > 40 then y = (40 * A) + (z * B)
if x1 < 40 and x2 > 40 then y = z2 * B
if x1 > 40 and x1 + x2 > 80 then y = z3 * B

Basically a formula that will calculate how much I've earned if I've worked
40 hours in pay period 1 OR pay period 2.
If i hit overtime in week 1 (for example 70 hours), and only worked 20 hours
in pay period 2, then anything above 20 hours in week 2 is also overtime rate.

i see that it's only necessary to calculate that once, instead of twice.
only checking for week 1's overtime, because if i haven't hit overtime in
week 1 the only way I'll get > 80 is getting more than 40 hours in week 2.

If this is too complicated for excel, I'll port my stuff and do it in either
C or Visual basic...

Thanks for any help or insight


~Benjamin

 

[spezticle]

I fixed a few of my variable declarations and math, etc. this should be
proper now:


if x1 < 40 then y1 = x1 * A
if x1 > 40 then y1 = (40 * A) + (z1* B)
if x2 < 40 then y2 = x * A
if x2 > 40 then y2 = (40 * A) + (z2 * B)
if x1 > 40 and x1 + x2 > 80 then y2 = z3 * B
y3 = y1+y2

 

[Roger Govier]

Hi

if I have understood you correctly, then the following should work
=MIN(40,x1)*A+MAX(0,x1-40)*B+MIN(40,x2)*A+MAX(0,x2-40)*B+MAX(0,(x1+x2)-80)*B

 

[David Biddulph]

No, I think that you've still got some work to do to get your requirements
straight, Benjamin.

You said

z1 = hours week 1 < 40
z2 = hours week 2 < 40
z3 = hours week 1 + week 2 < 80 but perhaps you intended
40 and >80, not <40 and <80 ?

Also your

if x1 > 40 and x1 + x2 > 80 then y2 = z3 * B

seems liable to give a pretty poor return. Look at the example of 41 hours
in each of the 2 periods, and it looks as if you're only going to pay for 2
hours in the second period and not for the remaining 39 hours. I don't
think I'm going to work for your company. :-(

If you haven't tried out your formulae by hand with some simple examples and
checked the logic, then you can't expect Excel to get it right. Come back
to us when you've decided what you need. It's certainly not too complicated
for Excel, and neither C nor Visual basic will give the right answser if you
ask it the wrong question.