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

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(A1100000,11%,IF(A179999,10%,IF(A149999,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.



FloridaMaggie wrote:
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

Take a look here

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

--
Regards,

Peo Sjoblom

(No private emails please)


"FloridaMaggie" wrote in message
...
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