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
- Each parameter has a minimum and maximum
- first parameter has ( or below )
- Last parameter has ( double what ever is over 100% to a max of 25% )
Looking forward to hearing from you community
21 Replies
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.
- 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 categoriesThanks in advance , looking forward to hearing from you For such cases I keep SUMPRODUCT() in mind, but doesn't matter - yes, most probably not IFS or like.
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 categoriesThanks in advance , looking forward to hearing from you
Without seeing an example of the spreadsheet you're working with, all one could say would be somewhat hypothetical.
Having said that, after reading this part of your request "need help with a complected nested IF formula" --- with the appearance at least that you're wanting a single formula to do all that, my response is "NO! DON'T DO IT!"
Not that it's not possible. I've seen, even written, long nested formulas. But they really become next to impossible to decipher when the inevitable happens, i.e., when it starts producing under some circumstances results that are unexpected or that seem off-base.
Far better--and this is generally what textbooks recommend--to break the process down into manageable bites. Use "helper columns" to register intermediate results. Hide them from view, if you like, once they've been shown to be reliable.
Having said that, if the situation is amenable to this, use the IFS function rather than deeply nested, multiple IF functions. IFS yields far more intelligible functions. But I really think what you've described would benefit from being broken into multiple columns, so each step could be clearly evaluated.
If you want further help though, I highly recommend posting a copy, or at least a representative sample, of the spreadsheet you're working on, with some further clarification of what all those conditions are.
- 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 categoriesThanks in advance , looking forward to hearing from youFor such model if we define commission levels as on the left
formula to calculate could be
=IF(E5>1,1+(E5-1)*2,IF(E5>95%,E5,LOOKUP(E5,$B$5:$B$9,$C$5:$C$9)))
