Menu
Can you calculate the sales commission? [homework]
Excel Challenges - 86 comments
Imagine you run a cute little pastry in Rome (Italy). To boost the sales you have a 2 person sales team. Caterina & Antonio. Caterina is the manager & Antonio, her assistant.
Apart from basic salary, they will also receive sales commission. This comes from a portion of net profit allocated to “incentive pool”. The sales commission is decided as below:
- First €25,000 goes to Caterina
- Any amount between €25,000 and €60,000 is split in 70:30 in favor of Caterina
- Any amount beyond €60,000 is split 50:50 between Caterina & Antonio
Your job: Write a formula to calculate the sales commission:
Assuming,
- Cell A1 ( named incentive ) contains total amount in incentive pool
- Cell A2 ( named first.split ) contains €25,000
- Cell A3 ( named second.split ) contains €60,000
- Cell B2 ( named first.ratio ) contains 70%
- Cell B3 ( named second.ratio ) contains 50%
What formula gives Caterina’s commission?
And what formula gives Antonio’s commission?
Post your answers in comments section
Go ahead and share your answers.
PS: This homework problem inspired from a question in our forum posted by Robbie.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « Open & Save files faster in Excel 2013 [quick tip] | Calculate Pi by throwing Frozen Hotdogs ! » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
86 Responses to “Can you calculate the sales commission? [homework]”
=max(second.split-first.split-incentive,0)*(1-first.ratio)+max(incentive-second.split, 0)*(1-second.ratio)
Vad's is certainly shorter that what I came up with, but I can't get it to work. Here's mine:
(some users may need to change semi-colons to commas)
- juanito
=MIN(first.split;incentive)+first.ratio*MIN(second.split-first.split;incentive-first.split)*(incentive>first.split)+second.ratio*(incentive-second.split)*(incentive>second.split)
Here is the answer.
=IF(incentive>second.split,(incentive-second.split)*(1-second.ratio)+(second.split-first.split)*(1-first.ratio),IF(incentive>first.split,(incentive-first.split)*(1-first.ratio),0))
The above formula is for Antonio. Below formula is for Caterina
=incentive-IF(incentive>second.split,(incentive-second.split)*(1-second.ratio)+(second.split-first.split)*(1-first.ratio),IF(incentive>first.split,(incentive-first.split)*(1-first.ratio),0))
Antonio:
=IF(incentive>second.split,(incentive-second.split)*second.ratio,)+IF(incentive>=first.split,(MIN(incentive,second.split)-first.split)*(1-first.ratio),)
Caterina:
=IF(incentivesecond.split,(incentive-second.split)*second.ratio,)+IF(incentive>=first.split,(MIN(incentive,second.split)-first.split)*first.ratio,)
Oops
For Caterina it should read
=if(incentive < = second.split.....
Caterina:
=IF(incentivesecond.split,first.split+((second.split-first.split)*first.ratio)+(incentive-second.split)*second.ratio,first.split+(incentive-first.split)*first.ratio))
Antonio:
=IF(incentivesecond.split,(second.split-first.split)*(1-first.ratio)+(incentive-second.split)*second.ratio,(incentive-first.split)*(1-first.ratio)))
I'm sure there's a better way than nested IFs but this works.
Ben.
VB solution with Caterina's commission in C7 and Antonio's in C9:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cIn As Currency
Dim cIn1 As Currency
Dim cIn2 As Currency
Dim cIn3 As Currency
Dim cIn4 As Currency
cIn = Cells(1, 1).Value
If cIn 25000 And cIn 60000 Then
cIn1 = 35000 * 0.7
cIn2 = 35000 * 0.3
cIn3 = cIn - 60000
cIn4 = cIn3 * 0.5
Cells(7, 3).Value = 25000 + cIn1 + cIn4
Cells(9, 3).Value = cIn2 + cIn4
End If
End Sub
Caterina:
=MIN(incentive,first.split) + MAX(MIN(incentive,second.split)-first.split,0)*first.ratio + MAX(incentive-second.split,0)*second.ratio
Antonio:
=MAX(MIN(incentive,second.split)-first.split,0)*(100%-first.ratio) + MAX(incentive-second.split,0)*(100%-second.ratio)
This is exactly how I did it as well.
My formula for Caterina's was the same.
For Antonio, I had: =incentive - caterina
Where caterina was the cell containing the Caterina formula.
Hi Chandoo.
=MIN(incentive,first.split)+first.ratio*MIN(MAX(0,incentive-first.split),second.split-first.split)+second.ratio*(MAX(0,incentive-second.split))
Regards
Caterina:
=IF(Incentive>Second.Split,first.split+((Incentive-first.split)*Second.ratio),IF(Incentive>first.split,first.split+((Incentive-first.split)*First.ratio),Incentive))
Antonio:
=IF(Incentive>Second.Split,((Incentive-first.split)*Second.ratio),IF(Incentive>first.split,((Incentive-first.split)*(1-First.ratio)),""))
=IF(incentive<=first.split,incentive,IF(incentivesecond.split,((incentive-second.split)*second.ratio)+((second.split-first.split)*first.ratio)+first.split,"F")))
So for 25000 she would get 25000
for 60000 she would get 49500
for 85000 she would get 62000
i added figures just to see if i understood the question right lol ... so if anyone matches these ill be happy
=IF(incentive<=first.split,incentive,IF(incentivesecond.split,((incentive-second.split)*second.ratio)+((second.split-first.split)*first.ratio)+first.split,"F")))
looked at my formula in post and it didnt paste properly (missing pieces ???) so will try posting it again
ok its still not pasting in the formula properly so probably best not to allow this post through as it would confuse others if they try it out
For the Boss (Named incentive.manager )
=MIN(first.split,incentive)
+first.ratio*IF(incentive<first.split,0,MIN(incentive-first.split,first.split))
+second.ratio*IF(incentive<second.split,0,incentive-second.split)
Now, the underling's is easy
= incentive - incentive.manager
Caterina: =MIN(incentive,first.split)+(first.ratio*MIN(MAX(incentive-first.split,0),second.split-first.split))+(second.ratio*MAX(incentive-second.split,0))
Antonio: =((1-first.ratio)*MIN(MAX(incentive-first.split,0),second.split-first.split))+((1-second.ratio)*MAX(incentive-second.split,0))
I couldn't make it shorter...
=MIN(incentive,first.split)+first.ratio*MIN(MAX(0,incentive-first.split),second.split-first.split)+second.ratio*MAX(0,incentive-second.split)
Here is an option without using IF. I wrote this before seeing Bigger Don's answer, but mine is very similar. I agree with Bigger Don's answer for the underling being the easiest, but I wasn't sure if that was the homework:
Caterina: =MIN(incentve,first.split)+(MAX(first.split,(MIN(incentve,second.split)))-first.split)*first.ratio+(MAX(incentve,second.split)-second.split)*second.ratio
Antonio: =(MAX(first.split,(MIN(incentve,second.split)))-first.split)*(1-first.ratio)+(MAX(incentve,second.split)-second.split)*(1-second.ratio)
I know some people hate VLOOKUP, but is nice to resolve this...
Caterina:
=if.error(VLOOKUP(incentive;$A$2:$B$3;2;TRUE)*incentive;incentive)
for Antonio
=if.error((1-VLOOKUP(incentive;$A$2:$B$3;2;TRUE))*incentive;0)
For Manager
=IF(Incentivefirst.spilt,Incentivesecond.spilt,first.spilt+(second.spilt-first.spilt)*first.ratio+(Incentive-second.spilt)*second.ratio)))
For Assistant
=IF(Incentivefirst.spilt,Incentivesecond.spilt,(second.spilt-first.spilt)*(1-first.ratio)+(Incentive-second.spilt)*(1-second.ratio))))
Mine is slightly different, but same results
=MIN(incentive,first.split)
+(MIN(MAX(0,incentive-first.split),second.split-first.split)*first.ratio)
+(MAX(incentive-second.split,0)*second.ratio)
Second formula is simply =incentive-caterina
Hi Chandoo,
If you can post the exact output for some given data, that will resolve the case...
Regards,
Faseeh
For 26000, The commision should be 1000*0.3 =300 ...
=IF(incentive>=25000,incentive-25000,incentive)*LOOKUP(incentive,{0,25000,60000},B1:B3)
Sorry, 1000 * 0.7 = 700, but formula still works... for first question
There goes another one:
I think than we can simplify this a bit more, but this is around MEDIAN.
for Caterina.
=IF(incentive<first.split;incentive;if(incentive=MEDIAN(A1:A3);first.ratio*incentive;second.ratio*incentive))
for Antonio
=IF(incentive<first.split;0;IF(incentive=MEDIAN(A1:A3);(1-first.ratio)*incentive;(1-second.ratio)*incentive))
NOTE:
In this solution if the incentive is 60000, then still splits 70-30.
=MIN(first.split,Incentive)+first.ratio*MAX(MIN(Incentive-first.split,second.split-first.split),0)+second.ratio*MAX(Incentive-second.split,0)
=IF(AND((Incentive>first.split),(Incentivesecond.split,Incentive*second.ratio))
For Antonio=SUMPRODUCT(--(Incentive>first.split), MIN(Incentive-first.split, second.split-first.split), 1-first.ratio)+SUMPRODUCT(--(Incentive>second.split), Incentive-60000, 1-second.ratio)
For Caterina = Incentive-Antonio.commission
for Caterina
=MIN(incentive,first.split) + (incentive>=first.split)*((incentive-first.split)*first.ratio) - MAX(incentive-second.split, 0)*(first.ratio-second.ratio)
for Antonio
=incentive-caterina
Caterina
=MIN(first.split,incentive)+first.ratio*MIN(incentive-first.split,second.split-first.split)+second.ratio*MAX(0,incentive-second.split)
Antonio
=(1-first.ratio)*MIN(incentive-first.split,second.split-first.split)+(1-second.ratio)*MAX(0,incentive-second.split)
Hi,
I prefer to set my variables outside the worksheet just to have them in one place, like this:
With
C.Rates ={1,0.7,0.5}
A.Rates = 1-C.Rates
C.Levels ={0,7.5,12}
A.Levels =-C.Levels
End
Commission(C) =SUM(((Incentive-C.Levels)>0)*C.Levels)+Incentive*INDEX(C.Rates;SUM(N((Incentive-C.Levels)>0))) +CSE
Commission(A) =SUM(((Incentive-A.Levels)>0)*A.Levels)+Incentive*INDEX(A.Rates;SUM(N((Incentive-A.Levels)>0))) +CSE
The commission formula is symmetrical.
Caterina=MIN(incentive,first.split)+first.ratio*MAX(0,MIN(incentive,second.split)-first.split)+second.ratio*MAX(0,incentive-second.split)
Antonio= incentive - Caterina
Here is a pic Micky Avidan shared about this problem.
Hi, Chandoo, and all,
1) Thanks for posting in my behalf.
2) To my opinion - the huge advantage in my approach is the flexibility.
The formula stays "simple" also in cases of a large amount of splits.
I mainly, use such a formula for IRS progressive calculations.
PS: Wouldn't it be a great benefit, for this site, if we could present a picture, by ourselves, without bothering you ?
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
You are welcome Michael. I would love to allow users to post pictures. I just don't know how to with our WordPress native comments. It doesn't allow such thing. I suggest upload the pictures to a free hosting site like flickr and pasting the link here in comments.
NP.
Testing...
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
Edit by Hui...
I wrapped your URL in an [Img SRC=" URL"/] wrapper
But use < > brackets instead
Refer: http://chandoo.org/wp/2011/11/04/fancy-posts-using-html-display-codes/
TEST #2:
TEST #3:
Here is a similar approach, which seems easier to understand.
Data Setup:
---------------------------
A B
1 75000
2 0 100%
3 25000 70%
4 60000 50%
---------------------------
where CELL A1 contains total incentive amount
Formula:
=SUMPRODUCT(--(A1>A2:A4), A1-A2:A4, B2:B4-B1:B3)
I do it with a little tweak...
A6 = Incentive Pool
A1 = 0
A2 = 25001
A3 = 60001
B1 = 100%
For Caterina:
=SUM(LOOKUP(ROW(INDIRECT(("1:"&A6))),A1:B3))
Control Shift Enter
For Antonia
=Incentive Pool - Incentive for Caterina
Limitations:
Incentive must be a whole number and <= 1048576
Caterina’s commission=IF(A125000,A160000,(25000+(60000-25000)*0.7+(A1-60000)*0.5))))
Caterina's Com."=IF(A125000,A160000,(25000+(60000-25000)*0.7+(A1-60000)*0.5))))"
Caterina's Com."=IF(A125000,A160000,(25000+(60000-25000)*0.7+(A1-60000)*0.5))))"
Sorry Chandoo!! Trying to paste the formula but showing after submitting the comment it is not showing correctly.....
...and this is a good reason for allowing to embed(!) a picture into the post.
Michael (Micky) Avidan
@Micky
Also have a read of:
http://chandoo.org/wp/2011/11/04/fancy-posts-using-html-display-codes/
Caterina's commission:
=IF(incentive=60000,first_split+first_ratio*(second_split-first_split)+second_ratio*(incentive-second_split),first_split+first_ratio*(incentive-first_split)))
Antonio's commission:
=incentive-IF(incentive=60000,first_split+first_ratio*(second_split-first_split)+second_ratio*(incentive-second_split),first_split+first_ratio*(incentive-first_split)))
Mine's not elegant.
For Caterina: IF(incentivefirst.split,incentivesecond.split,first.split+second.ratio*(incentive-first.split))))
For Antonio: Incentive - Caterina 🙂
Mine's not elegant.
IF(incentivefirst.split,incentivesecond.split,first.split+second.ratio*(incentive-first.split))))
For Antonio: Incentive – Caterina 🙂
caterina: =CHOOSE(RANK(incentive,$A$1:$C$1),
((incentive-second.split)*second.ratio)+((second.split-first.split)*first.ratio)+first.split,
((incentive-first.split)*first.ratio)+first.split,
incentive)
antonio: =CHOOSE(RANK(incentive,$A$1:$C$1),
((incentive-second.split)*second.ratio)+((second.split-first.split)*first.ratio)+first.split,
((incentive-first.split)*first.ratio)+first.split,
incentive)
Cool solution using RANK, new to me.
Does not work for me. Chandoo defined incentive and splits as A1:A3.
I believe that you put incentive and splits in row A.
I Replaced ...RANK(incentive,$A$1:$C$1)...
with ...RANK(incentive,$A$1:$A$3)...
and it works perfectly.
Mine is totally long winded lol!!!
Caterina = =IF(incentive>=first.split,first.split+IF(AND(incentive>first.split,incentivesecond.split,incentive>first.split),(second.split-first.split)*first.ratio,0)+IF(incentive>second.split,(incentive-second.split)*second.ratio,0)),incentive)
Antonio = =IF(incentivefirst.split,incentivesecond.split,incentive>first.split),(second.split-first.split)*(1-first.ratio),0)+IF(incentive>second.split,(incentive-second.split)*second.ratio,0)))
Caterina.Commission cell calculated as follows:
=IF(Incentive<First.Split,Incentive,IF(Incentive<Second.Split,First.Split+(Incentive-First.Split)*First.Ratio,First.Split+(Second.Split-First.Split)*First.Ratio+(Incentive-Second.Split)*Second.Ratio))
Antonio.Commission easy as : =Incentive-Caterina.Commission
(but note that this means there is no cross-check)
Caterina's Commission
=IF(Incentive<Second.split,(Incentive-First.split)*First.ratio+First.split,(Incentive-Second.split)*Second.ratio+(Second.split-First.split)*First.ratio+First.split)
Antonio's commisson
=IF(Incentive<Second.split,((Incentive-First.split)*(1-First.ratio)),(Incentive-Second.split)*Second.ratio+(Second.split-First.split)*(1-First.ratio))
For Caterina:
=IF(incentive<first.split,incentive,IF(incentive<second.split,first.split+(incentive-first.split)*first.ratio,first.split+(second.split-first.split)*first.ratio+(incentive-second.split)*second.ratio))
For Antonio:
=IF(incentive<first.split,0,IF(incentive<second.split,(incentive-first.split)*(1-first.ratio),(second.split-first.split)*(1-first.ratio)+(incentive-second.split)*(1-second.ratio)))
Caterina's commission =
IF(incentive>second.split,(incentive-second.split)*0.5+49500,IF(incentive>first.split,(incentive-first.split)*0.7+first.split,incentive))
Antonio's commission:
=incentive-Caterina’s commission
An old trick using differential rates
Brackets = {0,25000,60000}
For Caterina:
Ratios = {100%,70%,50%}
Dif_ratios_Cat = {100%,-30%,-20%}
Caterina =SUMPRODUCT(--(Incentive>Brackets),Incentive-Brackets,Dif_ratios_Cat)
For Caterina:
Ratios = {0%,30%,20%}
Dif_ratios_Ant = {0%,30%,20%}
Antonio =SUMPRODUCT(--(Incentive>Brackets),Incentive-Brackets,Dif_ratios_Ant)
My mistake:
For Antonio:
Ratios = {0%,30%,20%}
Dif_ratios_Ant = {0%,30%,20%}
Antonio =SUMPRODUCT(–(Incentive>Brackets),Incentive-Brackets,Dif_ratios_Ant)
Another one, in both formulas use "--" not "-"
Caterina =SUMPRODUCT(--(Incentive>Brackets),Incentive-Brackets,Dif_ratios_Cat
Antonio =SUMPRODUCT(--(Incentive>Brackets),Incentive-Brackets,Dif_ratios_Ant)
Assuming that incentive is 100000, first 25 k goes to the Caterina .Next 35 K is shared in the ratio of 7:3 and finally remaining about in the ratio of 50:50. My Formula to Calculate;
Caterina's Share : =25000+(0.7*35000)+((Incentive-second.split)*0.5)
Antonio's Share : =Incentive- Caterina's Share
Regards
Rudra
Bit of Correction
Assuming that incentive is 100000, first 25 k goes to the Caterina .Next 35 K is shared in the ratio of 7:3 and finally remaining about in the ratio of 50:50. My Formula to Calculate;
Caterina’s Share : ==first.split+(0.7*(second.split-first.split))+((Incentive-second.split)*0.5)
Antonio’s Share : =Incentive- Caterina’s Share
Regards
Rudra
Here is my solution:
Caterina:
=MIN(A2;B2)+MIN(MAX(A2-B2;0);C2-B2)*D2+MAX(A2-C2;0)*E2
Antonio:
=MIN(MAX(A2-B2;0);C2-B2)*(1-D2)+MAX(A2-C2;0)*(1-E2)
When..
A2 = Incentive
B2 = First.Split
C2 = Second.Split
D2 = First.Ratio
E2 = Second.Ratio
Formula in C6
Antonio =IF(A1>=A3,IF(A1>A2,(A3-A2)*(1-A4))+IF(A1>=A3,(A1-A3)*A5),IF(AND(A1>=A2,A1<=A3),(A1-A2)*(1-A4),0))
Caterina =A1-C6
Caterina's
=MIN(A1;A2)+MIN(A1-MIN(A1;A2);A3-A2)*A4+MAX(A1-A3;0)*A5
Here is my aproach:
For Catherina:
=MIN(A1:A2)+A4*(MEDIAN(A1:A3)-A2)+A5*MAX(0,A1-A3)
For Antonio:
=(1-A4)*(MEDIAN(A1:A3)-A2)+(1-A5)*MAX(0,A1-A3)
Blessings!
Yet another formula, with Frequency
Need to have cell B1 filled with 100%
For Caterina:
=SUMPRODUCT(SMALL(A1:A3,ROW(A1:A3))*(B1:B3-OFFSET(B1:B3,1,)*(ROW(A1:A3)<INDEX(FREQUENCY(A1:A3,A1),1)))*(ROW(A1:A3)<=INDEX(FREQUENCY(A1:A3,A1),1)))
Antonio = A1- Caterina
Hi Chandoo,
i've used this plugin in the past, which works with the native wordpress comments system and allows commenters to upload images as part of their comment.
https://wordpress.org/plugins/comment-images/
Caterina = IF(incentive<first.split,incentive,first.split+MIN(incentive-first.split,second.split-first.split)*first.ratio+(incentive-first.split-MIN(incentive-first.split,second.split-first.split))*second.ratio)
Antonio = IF(incentive<first.split,0,incentive-Caterina)
Hi Chandoo and friends of chandoo.org,
I'm from germany and here are my versions of the formulas. I wrote it firstly in the german excel language. So I hope it's right in english.
Sorry, that are long formulas, but I like it.
Caterina:
=first.split+If((incentive-first.split)>60,(second.split-first.split)*first.ratio,"")+(incentive-first.split-If((incentive-first.split)>60,(second.split-first.split)*first.ratio,"")-If((incentive-first.split)>60,(second.split-first.split)*(1-first.ratio),""))*second.ratio
Antonio:
=incentive-Cateriana's commission or
=If((incentive-first.split)>60,(second.split-first.split)*(1-first.ratio),"")+(incentive-first.split-If((incentive-first.split)>60,(second.split-first.split)*first.ratio,"")-If((incentive-first.split)>60,(second.split-first.split)*(1-first.ratio),""))*second.ratio
Thank you and Bye-bye!
Gerhard
For Antonio commission:
IF(incentive>second.split;(incentive-second.split)*second.ratio+35.000*30%;IF(incentive>first.split;(incentive-first.split)*30%;0))
For Caterina commission:
incentive-Antonio commission
If the incentive pool is $100,000, is Caterina's commission $69,500 and Antonio's $30,500? Those are the answers my equations listed below spit out.
Caterina's commission
=A2+(B2*(A3-A2))+IF(A1>A3,((A1-A3)*0.5), (0))
Antonio's commission
=0.3*(A3-A2)+IF(A1>A3, ((A1-A3)*0.5), (0))
Not sure how to name cells yet so I just used the standard reference. Think these equations are right but I welcome anyone who may be willing to advise. Thanks!
Catarina in cell B5
=IF(A1<A2,A1,IF(A1<A3,A2+(A1-A2)*B2,A2+B2*(A3-A2)+(A1-A3)*B3))
Antonia in cell B6
A1-B5
I will Calculate Antonio's commision and then remaining amount will be Catarina's commission
Antonio's commision without use of If formula's is
=MAX(MIN(second.split-first.split,incentive-first.split),0)*(1-first.ratio)+MAX(incentive-second.split,0)*second.ratio
Catarina's Commission is = incentive - Antonio's commision
€ 25,001.00
€ 25,000 70%
€ 60,000 50%
Caterina
Cathrine =IF(incentive=first.split,incentivesecond.split,(((incentive-first.split)*second.ratio)+first.split),0)))
Antonio
=IF(AND(incentive>=first.split,incentivesecond.split,(incentive-first.split)*(1-second.ratio),0))
=IF(A1A2,A1<A3),A2+(A1-A2)*A4,A2+(A3-A2)*A4+(A1-A3)*A5))
The above formula is for Catarina's commission
Cathrine:
=IF(incentive=first.split,incentivesecond.split,(((incentive-first.split)*second.ratio)+first.split),0)))
Antonio:
=IF(AND(incentive>=first.split,incentivesecond.split,(incentive-first.split)*(1-second.ratio),0))
Ignore first not displayed properly.
By my calcs, If the Incentive pool was €70000
Catarina should receive €54500, Antonio should receive €15500
Catarina's formula is
=IF(Incentive>second.split,
first.split+(((Incentive-first.split)-(Incentive-second.split))*first.ratio)+((Incentive-second.split)*second.ratio),
IF(Incentive>first.split,first.split+(((Incentive-first.split))*first.ratio),
Incentive))
Antonio's formula is
=IF(Incentive>second.split,
((Incentive-first.split)-(Incentive-second.split))*(1-first.ratio)+((Incentive-second.split)*second.ratio),
IF(Incentive>first.split, ((Incentive-first.split))*(1-first.ratio),
0))
=MAX((commission-second.amount)*second.split,0)+MAX((MIN(commission,second.amount)-first.amount)*(1-first.split),0)
Here my answers:
for Catherine:
=IF(Incentive>85000,(Incentive-85000)*Second.Ratio+67000,IF(Incentive>25000,(Incentive-25000)*First.Ratio+25000,Incentive))
For Antonio:
=IF(Incentive>85000,(Incentive-85000)*Second.Ratio+18000,IF(Incentive>25000,(Incentive-25000)*(100%-First.Ratio)+25000,Incentive))
sorry I forget to change the formula for Antonio
here you are again:
=IF(Incentive>85000,(Incentive-85000)*Second.Ratio+18000,IF(Incentive>25000,(Incentive-25000)*(100%-First.Ratio),0))
Hi Chandoo
=IF(Incentive LTE 25000,Incentive,25000) +
=IF(Incentive GT 60000, 35000*70%, IF((Incentive-25000) GT 0,(A7-25000)*70%,0)) +
=IF(Incentive GT 60000,(A7-60000)*50%,0)