What is this formula doing? (Min/Max)

[vikki123us]

I have inherited the below formula. I believe it is wrong but I don't really know what's it's doing. It is supposed to take sales amounts and depending on where the sales person is in regards to their quota payout on a percentage tiered approach.

Can someone rewrite this formula so it works correctly?

FORMULA:
=ROUND(MIN(L79,(SUM(G72,J72)))*L83+MAX(0,(MIN(J79,(SUM(G72,J72)))-L79)*L84)+MAX(0,((SUM(G72,J72))-J79)*L85),2)+ROUND(MIN(L79,(SUM(H72,J72)))*L83+MAX(0,(MIN(J79,(SUM(H72,J72)))-L79)*L84)+MAX(0,((SUM(H72,J72))-J79)*L85),2)-(ROUND(MIN(L79,(SUM(G41,J41)))*L83+MAX(0,(MIN(J79,(SUM(G41,J41)))-L79)*L84)+MAX(0,((SUM(G41,J41))-J79)*L85),2)+ROUND(MIN(L79,(SUM(H41,J41)))*L83+MAX(0,(MIN(J79,(SUM(H41,J41)))-L79)*L84)+MAX(0,((SUM(H41,J41))-J79)*L85),2))

 

[Jonmo1]

Re: HELP - What is this formula doing? (Min/Max)

You could use the Formula Evaluation tool to step through each step of the formula and observe what it's doing.

But I think you'll be best off evaluating what the formula is 'supposed to do', and start a new formula from scratch.

Can you give a more detailed description of what it's supposed to be doing?
Tiered percentages can probably be accomplished in a simpler way. But details are needed.

 

[vikki123us]

Re: HELP - What is this formula doing? (Min/Max)

Thank you Jonmo1.

Let's see if I can explain this....

Each quarter I compile a list of deals a sales person has sold and is eligible for commissions. The formula should be taking the total sold in current quarter and backing out what was in prior quarters (as the list is an ongoing list and is just added to each quarter). Then it is supposed to tier out the payments based on how much they have sold to date. Taking into consideration that 50% commission is paid at signing and 50% is paid at invoicing.

Each sales person has a quota target and percentage tiers:

[TABLE="width: 372"]
<tbody>[TR]
[TD="colspan: 4"]Stretch Target[/TD]
[/TR]
[TR]
[TD]All Products[/TD]
[TD] [/TD]
[TD]50%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] $ 2,000,000.00 [/TD]
[TD] [/TD]
[TD] $ 1,000,000.00 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 4"]Tier Stretch Target[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Quota [/TD]
[TD]All Products[/TD]
[TD]Total Quota %[/TD]
[/TR]
[TR]
[TD]0 - 50%[/TD]
[TD] Tier 1 [/TD]
[TD]1.50%[/TD]
[TD]3.0%[/TD]
[/TR]
[TR]
[TD]50 - 100%[/TD]
[TD] Tier 2 [/TD]
[TD]3.00%[/TD]
[TD]6.0%[/TD]
[/TR]
[TR]
[TD]> 100%[/TD]
[TD] Tier 3 [/TD]
[TD]4.50%[/TD]
[TD]9.0%[/TD]
[/TR]
</tbody><colgroup><col span="4"></colgroup>[/TABLE]



I'm not sure what the person before me was doing. But I do know that the Total Quota % (3, 6, 9) is what is in the sales persons letter. So I don't understand the need to half the percentages as they did in the "All Products" column.

 

[Jonmo1]

Re: HELP - What is this formula doing? (Min/Max)

So are the tiers cummulative ?

Say someone did 1,500,000
Do you get 3% on the 1,000,00 + 6% on the next 500,000
Or
Do you get 6% on the whole 1,500,000


Can you post a few examples, with your expected results and how you came to those results ?

 

[vikki123us]

Re: HELP - What is this formula doing? (Min/Max)

Yes that is how it works. But I think I may have answered my own question. Since it's 50% at contract date and 50% at invoicing that would explain why the percentages where halved.

I think I better understand the formula now. Is there an easier way to write this?

 

[Jonmo1]

Re: HELP - What is this formula doing? (Min/Max)

'Easier', that's a matter of opinion..

But there are definately alternate methods and shorter methods.

Here's a tutorial I found.
https://www.excelcampus.com/modeling/calculate-commissions-with-tiered-rate-structure/