Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
advanced 'if' multiply
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
advanced 'if' multiply
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
advanced 'if' multiply
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
look up and multiply | Excel Discussion (Misc queries) | |||
multiply, then add | Excel Discussion (Misc queries) | |||
add value of 4 cells, multiply by 3 subtract 72 multiply by 80% | Excel Discussion (Misc queries) | |||
Advanced Conditional Formatting Ideas Needed! (ok, maybe not that advanced...) | Excel Discussion (Misc queries) | |||
multiply | Excel Worksheet Functions |