fbpx
Search
Close this search box.

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.

Chandoo

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:

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

86 Responses to “Can you calculate the sales commission? [homework]”

  1. Vad says:

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

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

    • 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
    For Caterina it should read
    =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)

  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)

  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:

    Here is a pic Micky Avidan shared about this problem.

    Formula logic for Sales Commission calculation - Micky Avidan

    • 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

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

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

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

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

  60. Julian G says:

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

  61. Tadeus says:

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

  62. Tadeus says:

    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)

Leave a Reply


« »