Forum Discussion

Yousef70's avatar
Yousef70
Brass Contributor
Jul 12, 2020

Nested If formula

Dears ...   Kindly , need help with a complected nested IF formula to calculate commission as follows :-   Daily , weekly , monthly basis  4 categories 5 to 6 parameters for each category  Ea...
Formulas and Functions
Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Jul 12, 2020

Yousef70 

My two cents.

The solution would probably end in having a helper table (or even more) and lookup functions or SUMIFS() to get the results.

 

  • Yousef70's avatar
    Yousef70
    Brass Contributor
    Jul 12, 2020

    Detlef_Lewin 

     

    Kindly find attached a screenshot for the excel sheet , also , i will be adding details as follows :-
     
    1. My entry will be in cell AE33 formula at cell AE34 ( i will be entering a sales number )
    2. Total commission is 300 JD's divided into 4 categories as follows :-
    3. Sales at 70% of total commission( sales at 30000 JD's )
    4. Cash control over or short at 15%  of total commission 
    5. Internal consumption at 7.5% of tatal commission 
    6. Destroyed goods control at 7.5% of total control 
    7. In My opinion , one formula with all 5 parameters would work for all categories 
    8. Let us take sales as it is the most important first parameter IF he achieves 84.999% and below from his daily sales he gets 0.000% from his sales commission ( daily sales at cell E34 his sales commission at cell G33 )
    9. Parameter 2 , IF he achieves 85% to 90% from his daily sales he gets 50% from his sales commission 
    10. Parameter 3 , IF he achieves 90.001% to 95% from his daily sales he gets 80% from his sales commission 
    11. Now the complex parameter 4 , IF he gets 95.001% to 100% from his daily sales , he gets same % accomplished ( for example , if he accomplishes 95.67% he gets it , any number between 95.001% and 100% he gets it as is )
    12. Another complex parameter 5 , IF he achieves 100.01% and above to a maximum 125% from his daily sales target , only the number above 100% will be double from his sales commission ( for example , if he achieves 102% only the 2% will 4% and calculated from his sales commission as extra effort 
     
    If this is doable , i will explain the other categories
     
    Thanks in advance , looking forward to hearing from you  
      
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 12, 2020

    Detlef_Lewin 

    For such cases I keep SUMPRODUCT() in mind, but doesn't matter - yes, most probably not IFS or like.

Resources