I am looking for a function that will allow me to compare a sales number to
lower column 1 and upper column 2, and return to me a percentage that's in
column 3. This will be used for commision percentage depending on $'s of
sales.
I am looking for a function that will allow me to compare a sales number to
lower column 1 and upper column 2, and return to me a percentage that's in
column 3. This will be used for commision percentage depending on $'s of
sales.
This formula will lookup a value that is greater or equal to the lookup value
=INDEX(C1:C20,MATCH(SMALL(B1:B20,COUNTIF(B1:B20,"<"&E1)+1),B1:B20,0))
where C1:C20 is the percentage and B1:B20 the upper level values. You might
also want to have a look at this if the commisions are variable
http://www.mcgimpsey.com/excel/variablerate.html
Regards,
Peo Sjoblom
"Duncan Help" wrote:
> I am looking for a function that will allow me to compare a sales number to
> lower column 1 and upper column 2, and return to me a percentage that's in
> column 3. This will be used for commision percentage depending on $'s of
> sales.
Peo Sjoblom wrote...
>This formula will lookup a value that is greater or equal to the
lookup value
>
>=INDEX(C1:C20,MATCH(SMALL(B1:B20,COUNTIF(B1:B20,"<"&E1)+1),B1:B20,0))
>
>where C1:C20 is the percentage and B1:B20 the upper level values. You
might
>also want to have a look at this if the commisions are variable
....
If the col A values in the row below are equal to the col B values in
the row above, e.g.,
___0 1000 5%
1000 2000 8%
2000 5000 11%
5000 10000 14%
and if the col A and B values were sorted in ascending order, it'd be a
whole lot simpler to use
=VLOOKUP(E1-0.000001,A1:C20,3)
If the col A values in the row below are equal to one more than the col
B values in the row above, this could be simplified even further to
=VLOOKUP(E1,A1:C20,3)
>"Duncan Help" wrote...
>>I am looking for a function that will allow me to compare a sales
number to
>>lower column 1 and upper column 2, and return to me a percentage
that's in
>>column 3. This will be used for commision percentage depending on
$'s of
>>sales.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks