Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I Return a Value Using Multiple Criteria?
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 percent in the [PERCENTAGE RANGE, ie: 5-6.99%], then they receive a certain bonus [PAYOUT AMOUNT] based on their [BONUS LEVEL]. The static information is currently set up in a chart as follows: TITLE/BONUS LEVEL/INCREASE OVER SALES PLAN % RANGE/PAYOUT AMOUNT Store Manager 1 5.00%-6.99% $600.00 Store Manager 1 7.00%-8.99% $800.00 Store Manager 1 9.00%-10.99% $1,000.00 Store Manager 1 11.00%-1000.00% $1,200.00 Store Manager 2 5.00%-6.99% $400.00 Store Manager 2 7.00%-8.99% $600.00 Store Manager 2 9.00%-10.99% $800.00 Store Manager 2 11.00%-1000.00% $1,000.00 Associate Manager 1 5.00%-6.99% $400.00 Associate Manager 1 7.00%-8.99% $500.00 Associate Manager 1 9.00%-10.99% $600.00 Associate Manager 1 11.00%-1000.00% $700.00 Associate Manager 2 5.00%-6.99% $300.00 Associate Manager 2 7.00%-8.99% $400.00 Associate Manager 2 9.00%-10.99% $500.00 Associate Manager 2 11.00%-1000.00% $600.00 The info above is in SHEET 2. The €˜TITLE heading is in A1. The variable/monthly info is in SHEET 1 and contains the info that tells me who is entitled to receive a bonus, but ultimately I need to return the $ amount (From the €˜PAYOUT AMOUNT column) they should receive based on the chart above. The €˜TITLE column heading is A1, €˜% OVER SALES PLAN heading is in B1, etc. TITLE % OVER SALES PLAN Bonus Level Bonus Amount Store Manager 5.00% 2 ? Assistant Mgr 11.00% 4 ? Help? Thanks!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I Return a Value Using Multiple Criteria?
If you can take the increase range and aplit into two columns, you can use
SUMPRODUCT =SUMPRODUCT(Sheet1!$A$2:$A$500=$A2)*(Sheet1!$C$2:$ C$500<=$B2)*(Sheet1!$D$2:$D$500=$B2)*(Sheet1!$B$2 :$B$500=$C2)*$E$2:$E$500) Assumes your table is on Sheet1 from columns A - E (remember, the %'s are now in 2 columns). If column a matches your result sheet's A cell, begin range of % is less than or equal to bonus rate, end range is greater than or equal to bonus rate AND level matches, then return the payout amount listed. Please note, it does require the same cell range in all criteria and cannot be an entire column (Cannot use $B:$B, must be $B$2:$B$500 or some such range) "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 percent in the [PERCENTAGE RANGE, ie: 5-6.99%], then they receive a certain bonus [PAYOUT AMOUNT] based on their [BONUS LEVEL]. The static information is currently set up in a chart as follows: TITLE/BONUS LEVEL/INCREASE OVER SALES PLAN % RANGE/PAYOUT AMOUNT Store Manager 1 5.00%-6.99% $600.00 Store Manager 1 7.00%-8.99% $800.00 Store Manager 1 9.00%-10.99% $1,000.00 Store Manager 1 11.00%-1000.00% $1,200.00 Store Manager 2 5.00%-6.99% $400.00 Store Manager 2 7.00%-8.99% $600.00 Store Manager 2 9.00%-10.99% $800.00 Store Manager 2 11.00%-1000.00% $1,000.00 Associate Manager 1 5.00%-6.99% $400.00 Associate Manager 1 7.00%-8.99% $500.00 Associate Manager 1 9.00%-10.99% $600.00 Associate Manager 1 11.00%-1000.00% $700.00 Associate Manager 2 5.00%-6.99% $300.00 Associate Manager 2 7.00%-8.99% $400.00 Associate Manager 2 9.00%-10.99% $500.00 Associate Manager 2 11.00%-1000.00% $600.00 The info above is in SHEET 2. The €˜TITLE heading is in A1. The variable/monthly info is in SHEET 1 and contains the info that tells me who is entitled to receive a bonus, but ultimately I need to return the $ amount (From the €˜PAYOUT AMOUNT column) they should receive based on the chart above. The €˜TITLE column heading is A1, €˜% OVER SALES PLAN heading is in B1, etc. TITLE % OVER SALES PLAN Bonus Level Bonus Amount Store Manager 5.00% 2 ? Assistant Mgr 11.00% 4 ? Help? Thanks!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search for multiple criteria, and return yet another | Excel Worksheet Functions | |||
return multiple records matching multiple criteria | Excel Worksheet Functions | |||
Lookup Multiple Criteria return One answer | Excel Worksheet Functions | |||
match multiple criteria & return value from array | Excel Worksheet Functions | |||
Return result from multiple criteria | Excel Worksheet Functions |