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