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...
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
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.
Dear ...
how can i link the helper range to the cell of calculation and how to link the cell of calculation to cell where i will enter sales # ?
thanks in advance