Calculating Commisions based on a range of values

[Dianna Arambula]

I am trying to calculate a commision that is based on a
sales price. The example commision is a fixed fee for 1-
225000, 225001-275000, 275001-40000, .... For what ever
reason I have been unable to figure out how to accomplish
this. Ideally I can write a formula that checks the
sales price and determines the appropriate commision.

So
Sales Price Comm
$225,000 $2,000
$300,000 $2,500
$450,000 $4,000


Any advice and help is greatly appreciated!! Thanks to
all you excel wizards for your help....

 

[Dan E]

Dianna,

Build a small table somthing like
1 1000
225001 2000
275001 3000
300001 4000
325001 5000

Essentially, the first column will be the start of each
commision range and the second column is the
commision for that range (For example say this table
was in D2:E6)

If the value you wanted the commision for was in A2
Then you could lookup the commision using

=INDEX(E2:E6MATCH(A2,D261))

Dan E

 

[Dan E]

The formula got slightly messed up,

It should be...

=INDEX(E2:E6,MATCH(A2,D261))

Dan E