Array Formula?

[Guest]

I'm trying to create a spreadsheet for commission purposes.

Different commission percentages are paid based on a range of sales.

For example:

Sales between $80,000 to $100,000 earns 10% commission
Sales between $50,000 to $79,000 earns 8% commission, etc.

Does an array formula work for this? I've been trying to create one with no
luck

I appreciate any help

 

[Govind]

Hi,

If the number of ranges are not more than 7, you can use an if statement
to calculate.

For eg. using your given example, you can write a nested IF formula liks

=IF(A1>100000,11%,IF(A1>79999,10%,IF(A1>49999,8%,0%)))

However, if your range is more than 7, build a table using the least
value of the sales range and the corresponding commission %

Sales Value Commission %

0 2 (For sales from 0 to 19,999)

20000 5 (For sales from 20,000 to 49,999)

50000 7 (For sales from 50,000 to .....)

And then use the formula =VLOOKUP(A1,RANGE,2,TRUE) where A1 is where the
sales value is available and RANGE is the area where you have the sales
value & commission % matrix.

Regards

Govind.

 

[Peo Sjoblom]

Take a look here

http://www.mcgimpsey.com/excel/variablerate.html

--
Regards,

Peo Sjoblom

(No private emails please)