Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 83
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 118
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 83
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 118
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
if or match hmmmmmm [email protected] Excel Worksheet Functions 3 October 20th 09 11:01 PM
Index match within sumproduct Bony Pony[_2_] Excel Discussion (Misc queries) 2 February 11th 09 01:11 PM
Sumproduct with Match and Vlookup? adimar Excel Worksheet Functions 8 February 6th 08 12:09 AM
Help: Vlookup, Index, Match, or Sumproduct? Phrank Excel Worksheet Functions 3 February 14th 07 07:43 PM
Vlookup, match, or sumproduct? Phrank Excel Worksheet Functions 1 July 28th 06 07:15 AM


All times are GMT +1. The time now is 10:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"