PLEASE HELP! Return a Value Using Multiple Sources

E

eed

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!!
 
J

Jim Thomlinson

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...
 
E

eed

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 said:
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 said:
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!!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I Return a Value Using Multiple Criteria? 1
Return a Value Using Multiple Sources 3
Return a value based on multiple sources 1
Help creating a sales commission formula 3
matrix calculations 2
Switch? Needed value based upon where a # falls within a range of 3
Challenging Formula. Need help with writing a formula 3
Subtract forecasted usage & return a date? 3

Top