| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLEASE HELP! 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: Store Manager Level 1 Level 2 Level 3 Level 4 Exceeds Sales Plan 5.00%-6.99% $X $X $X $X Exceeds Sales Plan 7.00%-8.99% $X $X $X $X Exceeds Sales Plan 9.00%-10.99% $X $X $X $X Exceeds Sales Plan 11.00% + $X $X $X $X Associate Manager Level 1 Level 2 Level 3 Level 4 Exceeds Sales Plan 5.00%-6.99% $X $X $X $X Exceeds Sales Plan 7.00%-8.99% $X $X $X $X Exceeds Sales Plan 9.00%-10.99% $X $X $X $X Exceeds Sales Plan 11.00% + $X $X $X $X The info above is in SHEET 1. 'Store Manager' is in A1. Levels 1-4 for store manager are in B1-E1 The variable/monthly info is on SHEET 1 and contains the info that tells me who is entitled to receive a bonus, but ultimately I need to return the $AMOUNT (Bonus Amount column) they should receive based on the chart above. The TITLE heading is A1, % Over Sales Plan is in A2, 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
|
|||
|
|||
|
PLEASE HELP! Return a Value Using Multiple Sources
Does the level have to do with the percentage?
-- Alex *Remember to click "yes" if this post helped you. Thank you! "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: Store Manager Level 1 Level 2 Level 3 Level 4 Exceeds Sales Plan 5.00%-6.99% $X $X $X $X Exceeds Sales Plan 7.00%-8.99% $X $X $X $X Exceeds Sales Plan 9.00%-10.99% $X $X $X $X Exceeds Sales Plan 11.00% + $X $X $X $X Associate Manager Level 1 Level 2 Level 3 Level 4 Exceeds Sales Plan 5.00%-6.99% $X $X $X $X Exceeds Sales Plan 7.00%-8.99% $X $X $X $X Exceeds Sales Plan 9.00%-10.99% $X $X $X $X Exceeds Sales Plan 11.00% + $X $X $X $X The info above is in SHEET 1. 'Store Manager' is in A1. Levels 1-4 for store manager are in B1-E1 The variable/monthly info is on SHEET 1 and contains the info that tells me who is entitled to receive a bonus, but ultimately I need to return the $AMOUNT (Bonus Amount column) they should receive based on the chart above. The TITLE heading is A1, % Over Sales Plan is in A2, etc. TITLE % OVER SALES PLAN Bonus Level Bonus Amount Store Manager 5.00% 2 Assistant Mgr 11.00% 4 Help? Thanks!! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
PLEASE HELP! Return a Value Using Multiple Sources
Your souce table is very poorly set up for what you are trying to do... You
need the table to be set up as more of a paired values table as follows. Title Level Lower % Upper % Amount Store Manager 1 5% 7% xx Store Manager 1 7% 9% xx Store Manager 1 9% 11% xx Store Manager 1 11% 100% xx Store Manager 2 5% 7% xx Store Manager 2 7% 9% xx Store Manager 2 9% 11% xx Store Manager 2 11% 100% xx Store Manager 3 5% 7% xx Store Manager 3 7% 9% xx Store Manager 3 9% 11% xx Store Manager 3 11% 100% xx Store Manager 4 5% 7% xx Store Manager 4 7% 9% xx Store Manager 4 9% 11% xx Store Manager 4 11% 100% xx Assistant Manager 1 5% 7% xx Assistant Manager 1 7% 9% xx Assistant Manager 1 9% 11% xx Assistant Manager 1 11% 100% xx Assistant Manager 2 5% 7% xx Assistant Manager 2 7% 9% xx Assistant Manager 2 9% 11% xx Assistant Manager 2 11% 100% xx At that point using a simple SumProduct Formula you can get your amounts... In it's current format you will have a LOT of problems... -- HTH... Jim Thomlinson "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: Store Manager Level 1 Level 2 Level 3 Level 4 Exceeds Sales Plan 5.00%-6.99% $X $X $X $X Exceeds Sales Plan 7.00%-8.99% $X $X $X $X Exceeds Sales Plan 9.00%-10.99% $X $X $X $X Exceeds Sales Plan 11.00% + $X $X $X $X Associate Manager Level 1 Level 2 Level 3 Level 4 Exceeds Sales Plan 5.00%-6.99% $X $X $X $X Exceeds Sales Plan 7.00%-8.99% $X $X $X $X Exceeds Sales Plan 9.00%-10.99% $X $X $X $X Exceeds Sales Plan 11.00% + $X $X $X $X The info above is in SHEET 1. 'Store Manager' is in A1. Levels 1-4 for store manager are in B1-E1 The variable/monthly info is on SHEET 1 and contains the info that tells me who is entitled to receive a bonus, but ultimately I need to return the $AMOUNT (Bonus Amount column) they should receive based on the chart above. The TITLE heading is A1, % Over Sales Plan is in A2, etc. TITLE % OVER SALES PLAN Bonus Level Bonus Amount Store Manager 5.00% 2 Assistant Mgr 11.00% 4 Help? Thanks!! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
PLEASE HELP! Return a Value Using Multiple Sources
Would you use a macro to do this? If so, then let me know.
-- Alex *Remember to click "yes" if this post helped you. Thank you! "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: Store Manager Level 1 Level 2 Level 3 Level 4 Exceeds Sales Plan 5.00%-6.99% $X $X $X $X Exceeds Sales Plan 7.00%-8.99% $X $X $X $X Exceeds Sales Plan 9.00%-10.99% $X $X $X $X Exceeds Sales Plan 11.00% + $X $X $X $X Associate Manager Level 1 Level 2 Level 3 Level 4 Exceeds Sales Plan 5.00%-6.99% $X $X $X $X Exceeds Sales Plan 7.00%-8.99% $X $X $X $X Exceeds Sales Plan 9.00%-10.99% $X $X $X $X Exceeds Sales Plan 11.00% + $X $X $X $X The info above is in SHEET 1. 'Store Manager' is in A1. Levels 1-4 for store manager are in B1-E1 The variable/monthly info is on SHEET 1 and contains the info that tells me who is entitled to receive a bonus, but ultimately I need to return the $AMOUNT (Bonus Amount column) they should receive based on the chart above. The TITLE heading is A1, % Over Sales Plan is in A2, etc. TITLE % OVER SALES PLAN Bonus Level Bonus Amount Store Manager 5.00% 2 Assistant Mgr 11.00% 4 Help? Thanks!! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
PLEASE HELP! Return a Value Using Multiple Sources
Hi -
So I changed the format, but am not quite sure what my formula needs to look like specifically - could you provide some more detail? It would be much appreciated - thanks! "Jim Thomlinson" wrote: Your souce table is very poorly set up for what you are trying to do... You need the table to be set up as more of a paired values table as follows. Title Level Lower % Upper % Amount Store Manager 1 5% 7% xx Store Manager 1 7% 9% xx Store Manager 1 9% 11% xx Store Manager 1 11% 100% xx Store Manager 2 5% 7% xx Store Manager 2 7% 9% xx Store Manager 2 9% 11% xx Store Manager 2 11% 100% xx Store Manager 3 5% 7% xx Store Manager 3 7% 9% xx Store Manager 3 9% 11% xx Store Manager 3 11% 100% xx Store Manager 4 5% 7% xx Store Manager 4 7% 9% xx Store Manager 4 9% 11% xx Store Manager 4 11% 100% xx Assistant Manager 1 5% 7% xx Assistant Manager 1 7% 9% xx Assistant Manager 1 9% 11% xx Assistant Manager 1 11% 100% xx Assistant Manager 2 5% 7% xx Assistant Manager 2 7% 9% xx Assistant Manager 2 9% 11% xx Assistant Manager 2 11% 100% xx At that point using a simple SumProduct Formula you can get your amounts... In it's current format you will have a LOT of problems... -- HTH... Jim Thomlinson "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: Store Manager Level 1 Level 2 Level 3 Level 4 Exceeds Sales Plan 5.00%-6.99% $X $X $X $X Exceeds Sales Plan 7.00%-8.99% $X $X $X $X Exceeds Sales Plan 9.00%-10.99% $X $X $X $X Exceeds Sales Plan 11.00% + $X $X $X $X Associate Manager Level 1 Level 2 Level 3 Level 4 Exceeds Sales Plan 5.00%-6.99% $X $X $X $X Exceeds Sales Plan 7.00%-8.99% $X $X $X $X Exceeds Sales Plan 9.00%-10.99% $X $X $X $X Exceeds Sales Plan 11.00% + $X $X $X $X The info above is in SHEET 1. 'Store Manager' is in A1. Levels 1-4 for store manager are in B1-E1 The variable/monthly info is on SHEET 1 and contains the info that tells me who is entitled to receive a bonus, but ultimately I need to return the $AMOUNT (Bonus Amount column) they should receive based on the chart above. The TITLE heading is A1, % Over Sales Plan is in A2, 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 | |||
| Return a Value Using Multiple Sources | Excel Discussion (Misc queries) | |||
| Return a Value Using Multiple Sources | Excel Discussion (Misc queries) | |||
| Return a value based on multiple sources | Excel Discussion (Misc queries) | |||
| Data Validation with Multiple Sources | Excel Discussion (Misc queries) | |||
| Pivot table - multiple sources | Excel Discussion (Misc queries) | |||
Linear Mode
