Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
maybe match, maybe sumproduct hmmmmmm
Ok I Have a bonus stucture,
It goes on (12) 4 week cycles and and sales have to increase over the period to hit loest level of bonus. Ok to make it real easy, lets so Cola colb colc cold cole colf colg Period / low produciton / bonus /mid produciton /bonus /High production / b 1 100 $10 bonus, 100 <5000 $20 bonus 1000 $30 b 2 200 3 and lets say that sheet 2 A1= the period # and b2= the sales $ Pelase advsie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
maybe match, maybe sumproduct hmmmmmm
Hi, try this,
=IF(Sheet2!$B$2Sheet1!B2,IF(Sheet2!$B$2<Sheet1!D2 ,Sheet1!C2,IF(Sheet2!$B$2<Sheet1!F2,Sheet1!E2,Shee t1!G2)),"Lowest Level not Achieved") Also make sure in your columns "Low", "Mid" and "High" that you put the upper limit of that category's sales requirement e.g. LOW MID HIGH 100 500 1000 If this helps please click "Yes" <<<<<<<<<<< " wrote: Ok I Have a bonus stucture, It goes on (12) 4 week cycles and and sales have to increase over the period to hit loest level of bonus. Ok to make it real easy, lets so Cola colb colc cold cole colf colg Period / low produciton / bonus /mid produciton /bonus /High production / b 1 100 $10 bonus, 100 <5000 $20 bonus 1000 $30 b 2 200 3 and lets say that sheet 2 A1= the period # and b2= the sales $ Pelase advsie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
maybe match, maybe sumproduct hmmmmmm
so close,
But that two varible on sheet two; A1 the periof of the bonus b2 the amount on $ need to achieve the bonus. this function works great for period 1, but there are 12 peiod. morover a1 has text 1st, 2nd, 3rd and on... The tbl arrey Sheet col a; the period col b, the lowest bonus scale col c, the bonus amount col d, the middle bonus scale col e, the bonus amount col f, the highest bonus scale col g, the bonus amount The tble woudl look like this 1st $3250 b$500 $4500 b$1000 $7500 b$2000 2nd$5000 b$500 $7000 b$1000 $10000 b$2000 3rd $600 b$500 $8000 b$1000 $14000 b$ 2000 and on "BSc Chem Eng Rick" wrote: Hi, try this, =IF(Sheet2!$B$2Sheet1!B2,IF(Sheet2!$B$2<Sheet1!D2 ,Sheet1!C2,IF(Sheet2!$B$2<Sheet1!F2,Sheet1!E2,Shee t1!G2)),"Lowest Level not Achieved") Also make sure in your columns "Low", "Mid" and "High" that you put the upper limit of that category's sales requirement e.g. LOW MID HIGH 100 500 1000 If this helps please click "Yes" <<<<<<<<<<< " wrote: Ok I Have a bonus stucture, It goes on (12) 4 week cycles and and sales have to increase over the period to hit loest level of bonus. Ok to make it real easy, lets so Cola colb colc cold cole colf colg Period / low produciton / bonus /mid produciton /bonus /High production / b 1 100 $10 bonus, 100 <5000 $20 bonus 1000 $30 b 2 200 3 and lets say that sheet 2 A1= the period # and b2= the sales $ Pelase advsie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
maybe match, maybe sumproduct hmmmmmm
Here it is, it's long but it works.
=IF(Sheet2!$B$2=VLOOKUP(Sheet2!$A$5,Sheet1!A1:G12 ,2),IF(Sheet2!$B$2<VLOOKUP(Sheet2!$A$5,Sheet1!A1:G 12,4),VLOOKUP(Sheet2!$A$5,Sheet1!A1:G12,3),IF(Shee t2!$B$2<VLOOKUP(Sheet2!$A$5,Sheet1!A1:G12,6),VLOOK UP(Sheet2!$A$5,Sheet1!A1:G12,5),VLOOKUP(Sheet2!$A$ 5,Sheet1!A1:G12,7))),"Lowest Level not Achieved") If this helps, please click "Yes" <<<<<<<<<<<< " wrote: so close, But that two varible on sheet two; A1 the periof of the bonus b2 the amount on $ need to achieve the bonus. this function works great for period 1, but there are 12 peiod. morover a1 has text 1st, 2nd, 3rd and on... The tbl arrey Sheet col a; the period col b, the lowest bonus scale col c, the bonus amount col d, the middle bonus scale col e, the bonus amount col f, the highest bonus scale col g, the bonus amount The tble woudl look like this 1st $3250 b$500 $4500 b$1000 $7500 b$2000 2nd$5000 b$500 $7000 b$1000 $10000 b$2000 3rd $600 b$500 $8000 b$1000 $14000 b$ 2000 and on "BSc Chem Eng Rick" wrote: Hi, try this, =IF(Sheet2!$B$2Sheet1!B2,IF(Sheet2!$B$2<Sheet1!D2 ,Sheet1!C2,IF(Sheet2!$B$2<Sheet1!F2,Sheet1!E2,Shee t1!G2)),"Lowest Level not Achieved") Also make sure in your columns "Low", "Mid" and "High" that you put the upper limit of that category's sales requirement e.g. LOW MID HIGH 100 500 1000 If this helps please click "Yes" <<<<<<<<<<< " wrote: Ok I Have a bonus stucture, It goes on (12) 4 week cycles and and sales have to increase over the period to hit loest level of bonus. Ok to make it real easy, lets so Cola colb colc cold cole colf colg Period / low produciton / bonus /mid produciton /bonus /High production / b 1 100 $10 bonus, 100 <5000 $20 bonus 1000 $30 b 2 200 3 and lets say that sheet 2 A1= the period # and b2= the sales $ Pelase advsie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if or match hmmmmmm | Excel Worksheet Functions | |||
Index match within sumproduct | Excel Discussion (Misc queries) | |||
Sumproduct with Match and Vlookup? | Excel Worksheet Functions | |||
Help: Vlookup, Index, Match, or Sumproduct? | Excel Worksheet Functions | |||
Vlookup, match, or sumproduct? | Excel Worksheet Functions |