Forum Discussion
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...
As mathetes mentioned without sample file it's practically impossible to say something concrete. Even if one of us suggest one it could be quite different from your one. Starting from business logic and ending by technical details (e.g. parameters are numbers or texts).
Building the model forget for a while about formulas. That's not necessary could be nested IF, or IFS, that could be something else. First, build the model to which it'll be easy to apply formal logic with manual calculations. Formulas will be added on the top on next stage.
Kindly find attached a screenshot for the excel sheet , also , i will be adding details as follows :-
- My entry will be in cell AE33 formula at cell AE34 ( i will be entering a sales number )
- Total commission is 300 JD's divided into 4 categories as follows :-
- Sales at 70% of total commission( sales at 30000 JD's )
- Cash control over or short at 15% of total commission
- Internal consumption at 7.5% of tatal commission
- Destroyed goods control at 7.5% of total control
- In My opinion , one formula with all 5 parameters would work for all categories
- 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 )
- Parameter 2 , IF he achieves 85% to 90% from his daily sales he gets 50% from his sales commission
- Parameter 3 , IF he achieves 90.001% to 95% from his daily sales he gets 80% from his sales commission
- 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 )
- 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