Search

# Can you calculate the sales commission? [homework]

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?

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.

Thank you and see you around.

### Related articles:

 Written by Chandoo Tags: homework, Learn Excel, Microsoft Excel Formulas Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 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)

2. juanito says:

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)

3. Mehmet Gunal OLCER says:

=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))

• Mehmet Gunal OLCER says:

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))

4. Robert says:

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,)

5. Robert says:

Oops
=if(incentive < = second.split.....

6. Ben says:

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.

• Ben says:

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

7. QL says:

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)

• Matt Johnson says:

This is exactly how I did it as well.

• Mark says:

My formula for Caterina's was the same.

For Antonio, I had: =incentive - caterina

Where caterina was the cell containing the Caterina formula.

8. XOR LX says:

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

9. K Murugesan says:

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)),""))

10. John long says:

=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

• John long says:

=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

• John long says:

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

11. Bigger Don says:

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

12. Wanderlei Santos says:

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))

13. joão says:

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)

14. Todd Peskin says:

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)

15. Matías says:

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)

16. Tim Parham says:

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))))

17. guitarthrower says:

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

18. Faseeh says:

Hi Chandoo,

If you can post the exact output for some given data, that will resolve the case...

Regards,
Faseeh

19. Faseeh says:

For 26000, The commision should be 1000*0.3 =300 ...

=IF(incentive>=25000,incentive-25000,incentive)*LOOKUP(incentive,{0,25000,60000},B1:B3)

• Faseeh says:

Sorry, 1000 * 0.7 = 700, but formula still works... for first question

20. Matías says:

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.

21. Nagaraj says:

=MIN(first.split,Incentive)+first.ratio*MAX(MIN(Incentive-first.split,second.split-first.split),0)+second.ratio*MAX(Incentive-second.split,0)

22. GGraham says:

=IF(AND((Incentive>first.split),(Incentivesecond.split,Incentive*second.ratio))

23. Rich says:

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

24. 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

25. prerit says:

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)

26. Jeanbar says:

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.

27. Tom Nijsten says:

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

28. Chandoo says:

• Michael (Micky) Avidan says:

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

• Chandoo says:

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.

• Michael (Micky) Avidan says:

NP.
Testing...

Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

• Hui... says:

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/

• Michael (Micky) Avidan says:

TEST #2:

• Michael (Micky) Avidan says:

TEST #3:

• QL says:

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)

29. MF says:

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

30. Chandra Mohan Singh says:

Caterina’s commission=IF(A125000,A160000,(25000+(60000-25000)*0.7+(A1-60000)*0.5))))

31. Chandra Mohan Singh says:

Caterina's Com."=IF(A125000,A160000,(25000+(60000-25000)*0.7+(A1-60000)*0.5))))"

• Chandra Mohan Singh says:

Caterina's Com."=IF(A125000,A160000,(25000+(60000-25000)*0.7+(A1-60000)*0.5))))"

32. KM Zachariah says:

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)))

33. tenzing says:

Mine's not elegant.

For Caterina: IF(incentivefirst.split,incentivesecond.split,first.split+second.ratio*(incentive-first.split))))

For Antonio: Incentive - Caterina 🙂

34. tenzing says:

Mine's not elegant.

IF(incentivefirst.split,incentivesecond.split,first.split+second.ratio*(incentive-first.split))))

For Antonio: Incentive – Caterina 🙂

35. Rich Halecki says:

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.

36. Ardraaken says:

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)))

37. James says:

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)

38. Santosh says:

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))

39. Hubert says:

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)))

40. Medha says:

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))

• Medha says:

Antonio's commission:

=incentive-Caterina’s commission

41. Daniel says:

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)

• Daniel says:

My mistake:

For Antonio:

Ratios = {0%,30%,20%}
Dif_ratios_Ant = {0%,30%,20%}
Antonio =SUMPRODUCT(–(Incentive>Brackets),Incentive-Brackets,Dif_ratios_Ant)

• Daniel says:

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)

42. Rudra says:

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

43. Rudra says:

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

44. Cristof says:

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

45. Richy says:

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

46. Haz says:

Caterina's
=MIN(A1;A2)+MIN(A1-MIN(A1;A2);A3-A2)*A4+MAX(A1-A3;0)*A5

47. John Jairo V says:

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!

48. Alex says:

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

49. Mark says:

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/

50. Jeremy says:

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)

51. Gerhard says:

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

52. Pedro says:

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

53. Scott F says:

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!

54. frank mccraw says:

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

55. Yogesh Gupta says:

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

56. Khurrum Iqbal says:

€ 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))

57. Abdul Rehman says:

=IF(A1A2,A1<A3),A2+(A1-A2)*A4,A2+(A3-A2)*A4+(A1-A3)*A5))

• Abdul Rehman says:

The above formula is for Catarina's commission

58. Khurrum Iqbal says:

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.

59. Dale says:

By my calcs, If the Incentive pool was €70000
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))

60. Julian G says:

=MAX((commission-second.amount)*second.split,0)+MAX((MIN(commission,second.amount)-first.amount)*(1-first.split),0)

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))

63. Avnish Tiwari says:

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)

 « Open & Save files faster in Excel 2013 [quick tip] Calculate Pi by throwing Frozen Hotdogs ! »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.