IF, AND, BUt

[gsablan]

I need help doing the following formula.

I want to be able to plug in the sales amount and have the spreadshee
provide the correct commission based on the following commissio
schedule.

9% on amount between $1 and $250,000
8% on amount between $250,001 and $500,000
7% on amount between $500.001 and $750,000
6% on amount between $750,001 and $1,000,000
5% on amount between $1,000,000 and $2,000,000
4% on amount between $2,000,001 and $3,000,000
3% on amount between $3,000,001 and $5,000,000
2% on amount between $5,000,001 and $15,000,000
1% on amount over $15,000,000

can anyone help

 

[Frank Kabel]

Hi
1. create a lookup table with the following layout
A B
1 1 9%
2 250001 8%
.....

Now use the following formula
=VLOOKUP(value_to_search,'lookup'!$A$1:$B$20,2,0)

 

[Frank Kabel]

Hi
sorry, typo. Formula should read:
=VLOOKUP(value_to_search,'lookup'!$A$1:$B$20,2,1)

 

[Aladin Akyurek]

Assuming that E2:F10 houses your lookup table:

1 9%
250000 8%
500000 7%
750000 6%
1000000 5%
2000000 4%
3000000 3%
5000000 2%
15000000 1%


Try:

=IF(A1>=1,INDEX($F$2:$F$10,MATCH(A1,$E$2:$E$10,1)-(LOOKUP(A1,$E$2:$E$10)=A1)
),"")

or

=IF(A1>=1,(10-(MATCH(A1,$E$2:$E$10)-(LOOKUP(A1,$E$2:$E$10)=A1)))/100,"")

where A1 contains a sales amount of interest.

 

[hgrove]

gsablan wrote...
...

I want to be able to plug in the sales amount and have the
spreadsheet provide the correct commission based on the
following commission schedule.

9% on amount between $1 and $250,000
8% on amount between $250,001 and $500,000
7% on amount between $500.001 and $750,000
6% on amount between $750,001 and $1,000,000
5% on amount between $1,000,000 and $2,000,000
4% on amount between $2,000,001 and $3,000,000
3% on amount between $3,000,001 and $5,000,000
2% on amount between $5,000,001 and $15,000,000
1% on amount over $15,000,000

If you mean, for example, that $600,000 in sales would generate
commission of 9% * 250K + 8% * 250K + 7% * 100K = 49,500, then if yo
create the following table in, say, X2:Y10 (remove the underscores)

_______0 9%
__250000 8%
__500000 7%
__750000 6%
_1000000 5%
_2000000 4%
_3000000 3%
_5000000 2%
15000000 1%

and enter sales in A21, you could use the formula

=SUMPRODUCT(((A21>X2:X9)*(A21-X2:X9)-(A21>X3:X10)
*(A21-X3:X10)),Y2:Y9)+MAX(0,A21-X10)*Y10

If you want simpler formulas, change the table to

_______0 9%
__250000 -1%
__500000 -1%
__750000 -1%
_1000000 -1%
_2000000 -1%
_3000000 -1%
_5000000 -1%
15000000 -1%

you could shorten the formula to

=SUMPRODUCT((A21>X2:X10)*(A21-X2:X10),Y2:Y10