07-12-2020 07:58 AM
Dears ...
Kindly , need help with a complected nested IF formula to calculate commission as follows :-
Looking forward to hearing from you community
07-12-2020 09:18 AM - edited 07-12-2020 09:20 AM
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.
07-12-2020 11:55 AM
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.
07-12-2020 01:07 PM
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.
07-12-2020 01:38 PM
For such cases I keep SUMPRODUCT() in mind, but doesn't matter - yes, most probably not IFS or like.
07-12-2020 02:31 PM
07-12-2020 02:33 PM
07-12-2020 02:35 PM
07-12-2020 03:05 PM
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)))
07-13-2020 08:06 AM
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 :-
looking forward to hearing from you
07-13-2020 08:17 AM
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.
07-13-2020 09:44 AM
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
07-13-2020 10:33 AM
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
07-13-2020 11:09 AM
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.
07-14-2020 02:23 AM
Dear ...
Kindly find attached actual excel sheet , please , go to experimental Saleem's commission .
what i tried to do :-
thanks in advance
07-14-2020 11:23 AM
Thank you for the file. In it
- the only helper range you need is this one
- I'm not sure what do you enter in AM33 here
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)))
07-15-2020 04:38 AM
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 :-
the cells to work with highlighted in orange and dark orange
looking forward to hearing from you
07-15-2020 10:44 AM
If **bleep** such helper range
formula could be like
=$O$33*LOOKUP(AM35,$B$35:$B$39,$C$35:$C$39)
07-27-2020 07:36 AM
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 ...
07-27-2020 12:55 PM
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.
Other way is in Data->Data validation add only input message for the cell
It will be visible when you focus on the cell.