Need some serious help here

[rick0625]

I am making a commissions planner. So that it is understood what is trying to be accomplished here I will explain how they work.

There is a set goal for the month...lets say that goal is 20. If we reach this goal of 20 then we get paid out at 100% payout. For arguements sake lets say that payout is $1000. Until we reach this quota we are paid out at the percentage that we have obtained up until that point.

15 * 75% = $750 payout, 20 * 100% = $1000 payout

Example being: I made it 75% of the way and ended the month with 15. I would have made three quarters of the full one thousand dollar potential.

Here comes the hard part. There is an attainment table that looks like this:

This table basically shows that until you hit over 124.99% of your goal you will not tier up and begin getting paid out at higher percentages.

ie: If you are looking at the chart above someone could go above and beyond their goal and reach 170% of goal and the pay out would be :

1000 * 250% = 2500


In this speadsheet I have it set up like this:

|weighted amount| goal | monthly total | % to goal |
_________________________________________________

|__ $1000______ | 20_ |____ 15_____ |__ 75%__ |



As the monthly total rises ( which it does on a daily basis ) I would like it to calculate what the payout would be based on the tiered attainment table above.

So up until 124.99% it would calculate off the base percentage of the amount thus far but after 124.99% it would start tiering based off of the chart.

Any help would be greatly appreciated as I am new to excel formulas.

 

[RSXchin]

so... you're trying to find out where a person fits in your tiers, then muliply the column C value by the dollar amount sold?

If that's the case, you want to use a lookup table. I'll explain it more if that's it.

Either way, welcome to the boards.

 

[rick0625]

so... you're trying to find out where a person fits in your tiers, then muliply the column C value by the dollar amount sold?

If that's the case, you want to use a lookup table. I'll explain it more if that's it.

Either way, welcome to the boards.

Not exactly...think of it more like I am a saleperson trying to make a speadsheet auto calculate my paycheck based on commissions. They scale with the attainment table and I would like Excel to auto calc the tiers as my sales approach the different percentages for the tiers.