If greater than sum...

[Chemline]

Hi all,

I have had a good search and cant quite find what I am looking for so here goes....

I have a column of cells from i9 to i26 with each cell containing a value. In cell i32 I want it to find the sum of the above and then look at the sum and then multiply it by a predetermined commision rate if it falls with different bands. eg

if the sum total of i9:i26=5000 the commission rate would be 20%
if the sum total of i9:i26=15000 the commission rate would be 15%

and so on.

the commision rate bands are as follows:

0 - 10,000 = 20%
10,001 - 50,000 = 15%
50,001 - 125,000 = 12.5%
125,001 - 200,000 = 10%

I hope someone can help - I am stumped!!

Thanks

Dan

[NBVC]

try:

=Lookup(Sum(I9:I26),{0,10001,50001,125001},{.2,.15,.125,.1})*Sum(I9:I26)

[Chemline]

Genius!!! works like a charm. Having looked at what I had tried to do its no wonder it didnt work!

Thank you very much