Array Formula?
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
|