advanced 'if' multiply

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

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

variables:
A = base pay rate
B = overtime pay rate
x1 = hours worked in pay period 1
x2 = hours worked in pay period 2
y1 = money earned period 1
y2 = money earned period 2
y3 = total 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

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

--
Regards
Roger Govier

"spezticle" wrote in message
...
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

variables:
A = base pay rate
B = overtime pay rate
x1 = hours worked in pay period 1
x2 = hours worked in pay period 2
y1 = money earned period 1
y2 = money earned period 2
y3 = total 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

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.
--
David Biddulph

"spezticle" wrote in message
...
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

variables:
A = base pay rate
B = overtime pay rate
x1 = hours worked in pay period 1
x2 = hours worked in pay period 2
y1 = money earned period 1
y2 = money earned period 2
y3 = total 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