If Statement - no idea where to start!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mattymoo - 21 May 2008 19:10 GMT

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

Reply to this Message

Niek Otten - 21 May 2008 19:22 GMT

Look at the VLOOKUP() function

Here's an excellent tutorial:

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

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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
[quoted text clipped - 11 lines]
|
| Thanks

Reply to this Message

Mike H - 21 May 2008 19:29 GMT

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

> 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
[quoted text clipped - 11 lines]
>
> Thanks

Reply to this Message

Mattymoo - 21 May 2008 20:39 GMT

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

thanks

Pauline

> hi,
>
[quoted text clipped - 32 lines]
> >
> > Thanks

Reply to this Message

Gord Dibben - 22 May 2008 00:51 GMT

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

>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
[quoted text clipped - 11 lines]
>
>Thanks

Reply to this Message