Nested If formula

Brass Contributor

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

@Yousef70 

 

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.

@Yousef70 

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.

@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.

 

@Detlef Lewin 

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

@mathetes 

 

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  
  Capture.PNG

@Sergei Baklan 

 

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  
  Capture.PNG

@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  
  Capture.PNG

@Yousef70 

For such model if we define commission levels as on the left

image.png

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)))

 

@Sergei Baklan 

 

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 :-

 

  1. how can we combine all those formulas together in one formula to be in one cell 
  2. I only have one entry per day for sales 
  3. 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

@Yousef70 

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.

@Sergei Baklan 

 

Dear ...

 

I have learned excel by practice , i have added the helper , however i could not make it work 

 

can you connect with me via Quick Assist ?

 

i will be very great full to you

 

looking forward to hearing from you 

@Sergei Baklan 

 

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 

@Yousef70 

Add helper range into the suitable place, take formula and instead of sample file references use work file ones. If you have sample file it'll be easier to explain.

@Sergei Baklan 

 

Dear ...

 

Kindly find attached actual excel sheet , please , go to experimental Saleem's commission .

 

what i tried to do :-

  1. I added the range 
  2. I highlighted the cells , yellow for entry , gold for calculation 

 

thanks in advance 

@Yousef70 

Thank you for the file. In it

- the only helper range you need is this one

image.png

- I'm not sure what do you enter in AM33 here

image.png

I  assume per cent of sales (as an example 92%)

- also not sure what shall be returned into AM34, per cent of commission or commission itself. Assuming the latest and that full commission is on O33 (210 here), formula will be

=O33*IF(AM33>1,1+(AM33-1)*2,IF(AM33>95%,AM33,LOOKUP(AM33,$B$5:$B$9,$C$5:$C$9)))

@Sergei Baklan 

 

Dear Sir ...

 

Thank you for all you have done the formula is working perfectly you have made my day . Attached file to see 

If possible , need your help with second stage of the commission for cash control ( being short ) :-

as in file attached 5 parameters :-

  1. If cash short rang from 0.000 to -0.250 he gets 100% from commission   (45.000 JD's / month)
  2. If cash short rang from -0.251 to -0.500 he gets 75% from commission
  3. If cash short rang from -0.501 to -0.750 he gets 50% from commission 
  4. If cash short rang from -0.751 to -1.000 he gets 25% from commission
  5. If cash short rang from -1.000 and above he gets 0% from commission 

the cells to work with highlighted in orange and dark orange

 

looking forward to hearing from you 

@Yousef70 

If **bleep** such helper range

image.png

formula could be like

=$O$33*LOOKUP(AM35,$B$35:$B$39,$C$35:$C$39)

 

@Sergei Baklan 

Dear Sir ...

 

Kindly , accept my many thanks for all you have done to me , all calculations are spot on , my employee is very happy with the results ...

 

I have one question , can a hovering cell be done in excel ?

 

looking forward to hearing from you ... 

@Yousef70 

You may add a note (aka comment in old version of Excel) to the cell(s), keep it in hide mode. It will be visible when you hover the cell.

image.png

Other way is in Data->Data validation add only input message for the cell

image.png

It will be visible when you  focus on the cell.