Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return a Value Using Multiple Sources
I need to return a value (a $AMOUNT) based on the following information:
TITLE, LEVEL 1 2 3 or 4, and a percentage range. Ex: If the [TITLE, ie: Store Manager] exceeds their sales plan by a [PERCENTAGE RANGE, ie: 1-5%], then they receive a certain bonus [$AMOUNT] based on their [LEVEL]. The static information is currently set up in a chart as follows: Level 1 Level 2 Level 3 Level 4 Title 1 Percentage Range 1 $X $X $X $X Percentage Range 2 $X $X $X $X Percentage Range 3 $X $X $X $X Percentage Range 4 $X $X $X $X Title 2 Percentage Range 1 $X $X $X $X Percentage Range 2 $X $X $X $X Percentage Range 3 $X $X $X $X Percentage Range 4 $X $X $X $X The variable/monthly info is in a basic spreadsheet format and contains the info that tells me who is entitled to receive a bonus, but ultimately I need to return the $AMOUNT they should receive based on the chart above. Help? Thanks!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return a Value Using Multiple Sources
OK, some f thsi will depend on where your Title and Level are in your result
sheet. So, we'll assume title is in A2, Percent over target is in B2 and Level # is in C2 Let's assume the first table is for Store Manager and is on sheet2 in cells A1 - E5. =IF(A2=Sheet2!A1,INDEX(A1:E5,VLOOKUP(B2,Sheet2!A1: A5,1),C2+1)) You'd have to nest if's for each title available in the above, so: =IF(A2=Sheet2!A1,INDEX(A1:E5,VLOOKUP(B2,Sheet2!A1: A5,1),C2+1),IF(A2=Sheet2!A6,INDEX(A6:E10,VLOOKUP(B 2,Sheet2!A6:A10,1),C2+1))) would be a 2 level... would get it. "eed" wrote: I need to return a value (a $AMOUNT) based on the following information: TITLE, LEVEL 1 2 3 or 4, and a percentage range. Ex: If the [TITLE, ie: Store Manager] exceeds their sales plan by a [PERCENTAGE RANGE, ie: 1-5%], then they receive a certain bonus [$AMOUNT] based on their [LEVEL]. The static information is currently set up in a chart as follows: Level 1 Level 2 Level 3 Level 4 Title 1 Percentage Range 1 $X $X $X $X Percentage Range 2 $X $X $X $X Percentage Range 3 $X $X $X $X Percentage Range 4 $X $X $X $X Title 2 Percentage Range 1 $X $X $X $X Percentage Range 2 $X $X $X $X Percentage Range 3 $X $X $X $X Percentage Range 4 $X $X $X $X The variable/monthly info is in a basic spreadsheet format and contains the info that tells me who is entitled to receive a bonus, but ultimately I need to return the $AMOUNT they should receive based on the chart above. Help? Thanks!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return a Value Using Multiple Sources
Oh, and important note, the percent rage in yoru source should be the low end
of your range (i.e. - .01 then .05, etc.), not a range of numbers. the VLOOKUP will return the lowest value that is less than or equal to your B2 cell n that example. "eed" wrote: I need to return a value (a $AMOUNT) based on the following information: TITLE, LEVEL 1 2 3 or 4, and a percentage range. Ex: If the [TITLE, ie: Store Manager] exceeds their sales plan by a [PERCENTAGE RANGE, ie: 1-5%], then they receive a certain bonus [$AMOUNT] based on their [LEVEL]. The static information is currently set up in a chart as follows: Level 1 Level 2 Level 3 Level 4 Title 1 Percentage Range 1 $X $X $X $X Percentage Range 2 $X $X $X $X Percentage Range 3 $X $X $X $X Percentage Range 4 $X $X $X $X Title 2 Percentage Range 1 $X $X $X $X Percentage Range 2 $X $X $X $X Percentage Range 3 $X $X $X $X Percentage Range 4 $X $X $X $X The variable/monthly info is in a basic spreadsheet format and contains the info that tells me who is entitled to receive a bonus, but ultimately I need to return the $AMOUNT they should receive based on the chart above. Help? Thanks!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return a Value Using Multiple Sources
I've tried this and think I almost have it, but the formula doesn't seem to
be recognizing the percent ranges. (I did what you said below regarding this and only put the low end of my range in in each of those cells...still doesn't seem to work.) I am getting a $value returned for lowest end of the range and above; the dollar amounts are all coming from the $value in the the cells next to the first percentage range (Which should be 5% to 6.99%, but is now in my table as 5.00%, or the lowest value in my range) See below - this is how 'SHEET 2' is set up: TITLE (A1) Level 1(B1)Level 2 Level 3 Level 4 5.00% $X $X $X $X 7.00% $X $X $X $X 9.00% $X $X $X $X 11.00% $X $X $X $X TITLE (A6) Level 1 (B6)Level 2 Level 3 Level 4 5.00% $X $X $X $X 7.00% $X $X $X $X 9.00% $X $X $X $X 11.00% $X $X $X $X SHEET 1 is set up as follows: TITLE (A1) % OVER SALES PLAN Bonus Level Bonus Amount 5.00% 2 -2.00% 4 Thank you so much for all of your help!! "Sean Timmons" wrote: Oh, and important note, the percent rage in yoru source should be the low end of your range (i.e. - .01 then .05, etc.), not a range of numbers. the VLOOKUP will return the lowest value that is less than or equal to your B2 cell n that example. "eed" wrote: I need to return a value (a $AMOUNT) based on the following information: TITLE, LEVEL 1 2 3 or 4, and a percentage range. Ex: If the [TITLE, ie: Store Manager] exceeds their sales plan by a [PERCENTAGE RANGE, ie: 1-5%], then they receive a certain bonus [$AMOUNT] based on their [LEVEL]. The static information is currently set up in a chart as follows: Level 1 Level 2 Level 3 Level 4 Title 1 Percentage Range 1 $X $X $X $X Percentage Range 2 $X $X $X $X Percentage Range 3 $X $X $X $X Percentage Range 4 $X $X $X $X Title 2 Percentage Range 1 $X $X $X $X Percentage Range 2 $X $X $X $X Percentage Range 3 $X $X $X $X Percentage Range 4 $X $X $X $X The variable/monthly info is in a basic spreadsheet format and contains the info that tells me who is entitled to receive a bonus, but ultimately I need to return the $AMOUNT they should receive based on the chart above. Help? Thanks!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return a value based on multiple sources | Excel Discussion (Misc queries) | |||
Spreadsheet With Multiple Data Sources | Excel Worksheet Functions | |||
filter data from multiple sources | Excel Discussion (Misc queries) | |||
Data Validation with Multiple Sources | Excel Discussion (Misc queries) | |||
Pivot table - multiple sources | Excel Discussion (Misc queries) |