Forum Discussion
Nested If formula
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.
- 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
For 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)))Dear ...
Thanks a million for the effort you are putting into this .
I think we are getting closer to what i need , my comments as follows :-
- how can we combine all those formulas together in one formula to be in one cell
- I only have one entry per day for sales
- as for the commission , you only defined round numbers ( what about in between those numbers ? , also , what about decimals ?
looking forward to hearing from you
But that is one formula in one cell. In sample it's only result is illustrated for different sales per cent.
You add helper range at any place of your workbook as here in B4:C9. Using it one formula calculates per cent of commission (or commission itself) based on per cent of sales.
