I'm trying to develop a formula to calculate a sales team incentive bonus. A
bonus amount is calculated based on sales made, but then is adjusted 3 months
later based on the criteria below.


Less than 50% of sales still on the books 0% payable
50% to 60% of sales 25%
61% to 70% of sales 50%
71% to 80% of sales 75%
81% to 90% of sales 100%
Over 90% of sales 125%

I'm afraid I don't know where to start - can anyone point me in the right
direction?

Thanks

Look at the VLOOKUP() function

Here's an excellent tutorial:

http://www.contextures.com/xlFunctions02.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mattymoo" wrote in message ...
| I'm trying to develop a formula to calculate a sales team incentive bonus. A
| bonus amount is calculated based on sales made, but then is adjusted 3 months
| later based on the criteria below.
|
|
| Less than 50% of sales still on the books 0% payable
| 50% to 60% of sales 25%
| 61% to 70% of sales 50%
| 71% to 80% of sales 75%
| 81% to 90% of sales 100%
| Over 90% of sales 125%
|
| I'm afraid I don't know where to start - can anyone point me in the right
| direction?
|
| Thanks
|

hi,

You could use an IF statement but that can get unweildy. would you like an
alternative, if so try this. Build a table somewhere which in my case is in
A1 - B5 and enter your conditions looking like this:-

50.00% 25.00%
61.00% 50.00%
71.00% 75.00%
81.00% 100.00%
91.00% 125.00%

Note the left hand column is sorted ascending. Then this formula
=VLOOKUP(C1,$A$1:$B$5,2,TRUE)

the formula look looks at C1 and then looks for a closest match less than C1
in the table starting at 50% and returns the commission from the second
column.

Mike


"Mattymoo" wrote:

I'm trying to develop a formula to calculate a sales team incentive bonus. A
bonus amount is calculated based on sales made, but then is adjusted 3 months
later based on the criteria below.


Less than 50% of sales still on the books 0% payable
50% to 60% of sales 25%
61% to 70% of sales 50%
71% to 80% of sales 75%
81% to 90% of sales 100%
Over 90% of sales 125%

I'm afraid I don't know where to start - can anyone point me in the right
direction?

Thanks

Thank you both for your help. i'll give it a go and report back if I get stuck

thanks

Pauline

"Mike H" wrote:

hi,

You could use an IF statement but that can get unweildy. would you like an
alternative, if so try this. Build a table somewhere which in my case is in
A1 - B5 and enter your conditions looking like this:-

50.00% 25.00%
61.00% 50.00%
71.00% 75.00%
81.00% 100.00%
91.00% 125.00%

Note the left hand column is sorted ascending. Then this formula
=VLOOKUP(C1,$A$1:$B$5,2,TRUE)

the formula look looks at C1 and then looks for a closest match less than C1
in the table starting at 50% and returns the commission from the second
column.

Mike


"Mattymoo" wrote:

I'm trying to develop a formula to calculate a sales team incentive bonus. A
bonus amount is calculated based on sales made, but then is adjusted 3 months
later based on the criteria below.


Less than 50% of sales still on the books 0% payable
50% to 60% of sales 25%
61% to 70% of sales 50%
71% to 80% of sales 75%
81% to 90% of sales 100%
Over 90% of sales 125%

I'm afraid I don't know where to start - can anyone point me in the right
direction?

Thanks

With total sales in A1 and percentage of sales in B1, enter this formula in C1

=LOOKUP(B1,{0,50,61,71,81,91},{0,0.25,0.5,0.75,1,1 .25})*A1


Gord Dibben MS Excel MVP

On Wed, 21 May 2008 11:10:00 -0700, Mattymoo
wrote:

I'm trying to develop a formula to calculate a sales team incentive bonus. A
bonus amount is calculated based on sales made, but then is adjusted 3 months
later based on the criteria below.


Less than 50% of sales still on the books 0% payable
50% to 60% of sales 25%
61% to 70% of sales 50%
71% to 80% of sales 75%
81% to 90% of sales 100%
Over 90% of sales 125%

I'm afraid I don't know where to start - can anyone point me in the right
direction?

Thanks