Two Factor Sales Commission with Ranges - Help

[Jim Brewer]

Hello All,

I have a question that is a little bit more advanced than what I an used too. Attached is a spreadsheet with two different sales commission tables that are calculated in a similar fashion. As you can see "Net Sales Target Attainment" portion calculates the percentage that you are to your Attainment of you sales goal i.e. quota is 2900, if you sell 3900, you are 3900/2900 = 135% of Net Sales Attainment. This setup the y - axis of the (121%-160%) Commission range for the first factor. The second factor is along the x-axis which is a range that is connected to the sales of a particular product. Example is $501 dollars is sold of the product this would put the person in the ($500-$999) range. Thus combining the two factors would give the sales person a commission rate of 100%. The same applies for the second grid, however the x-axis has input of Poor, Fair, Good, etc. I would like to see if some one could help with showing me how to write a formula for the grid that will give me the correct Net Sales Commission Rate as well as the Renewals Commission Rate. Please see the attached. Thanks. -Jim

 

[AliGW]

First, change the row and column headings to straight numbers on the first table (you can use formatting to show the currency or percentage), then use this for the first calculation:

=INDEX($C$4:$G$9,MATCH(C24*100,$B$4:$B$9,1),MATCH(C22,$C$3:$G$3,1))*100

You can adapt this for the second. Example attached.