AL, BB, RB and BR are store names.
AL will need to transfer stock to either of BB, RB or BR if:
sale of AL >3 and STR of AL >90.
The store where the transfer will go must have atleast 3 pc sale, have less than 45 STR and their STR is lowest among the three stores (BB, RB, BR)
I required to return the store name in yellow cell which falls under these criterias.
Last edited by brajesh.sharma87; 07-14-2018 at 04:04 AM.
Welcome to the forum! Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!
- Use concise, accurate thread titles.
- Your post title should describe your problem, not your anticipated solution.
- Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
- Responding to a request to change your thread title by doing so is mandatory.
To change a title go to your first post, click EDIT then Go Advanced and change your title.
No help to be offered, please, until the OP complies with this request.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi @AliGW, I hope I've complied to the rules now.
I've worked on a solution which does my work but its a bit complicated and I am sure there is a way to achieve this with smaller formula. Please check attached file.
Sorry for not complying with the rule in the start.
Regards
Brajesh
Thank you! I'll have a look.
OK - what we don't want to have to do is back engineer what you have done, so please list here, explaining in words, each of the conditions you wish to test for and what they should return. It may be that a LOOKUP table might be the way forward.
e.g.
1. If C5 is bigger than ..., return F3.
2. If ...
I've explained the same way in the OP.
My main concern is:
say I am running two conditions, with first test in the formula 3 comes as true out of 7, now I want to chose 1 out of the previous 3 results with a different test. I am not sure how to explain it.
OK - how about you mock up a sample workbook with the outcomes you are looking for?
No, you didn't. You explained just a tiny bit of what you are trying to do in that long formula. It's up to you: some others may have time to sit and unpick it to work it out, but I don't I'm afraid. If I am going to help you, you need to provide the information I ask for.I've explained the same way in the OP.
I dont want to offend you with my reply but I guess you haven't opened my first attachment in the OP. Everything is clearly explained as per the sheet attached.
You may have opened my next post attachment hence you are confused. Please see if you or anyone else can help. If this is still not clear, I will just close the thread and will prepare a new example and post it.
I think this gets you what you want but I'm not completely sure, I don't know what you want done if none of the conditions are met for example or if the AL condition is not met so I am leaving that outcome as blank. For example,what if it is higher than 3 but less than or equal to 90?sale of AL >3 and STR of AL >90.
so anyway, this is what I worked out...
=IF(AND(C5<=3,D5<=90),"",IF(AND(MIN(G5,I5,K5)=G5,MIN(G5,I5,K5)<45,F5>2),F3,IF(AND(MIN(G5,I5,K5)=I5,MIN(G5,I5,K5)<45,H5>2),H3,IF(AND(MIN(G5,I5,K5)<45,J5>2),J3))))
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
You should not open a new thread on the same issue - there is no need, and in any case, it would be closed as a duplicate with a link back here.
No, I did not open the first attachment, only the second, which superceded it.
Please copy and paste whatever critical information is in that first workbook into your next response here. Thanks.
Uh oh, if it is different, I based my formula on the first. Oh well.No, I did not open the first attachment, only the second, which superceded it.
For any condition not matching in AL, return blankOriginally Posted by Sambo kid
For the other 3 stores, say if two stores doesn't fulfill the criteria then the remaining store name will come.
Put these numbers after the formula cell 2, 8 4,15 1,3
with you current formula, it comes as "FALSE", the result should be RB (4,15)
Thanks for taking out your time to help me.
yes, but see, from your original post...
So with your change for the numbers the RB store does not have the lowest among the three stores.have less than 45 STR and their STR is lowest among the three stores (BB, RB, BR)
Why don't you list the conditions in order of what needs to be met to happen for instance.
1st AL needs to have BOTH conditions met (this means AND instead of OR).
2nd The store to get the transfer has to have at least 3 AND less than 45 AND be the lowest among the three UNLESS one or two of the others have less than 3 AND (OR?) less than 45.
See where I'm going with this?
A problem when you use formulas (especially if/then) is that the formula often stops after it finds the first condition that meets the criteria.
Why indeed? I have asked ...Why don't you list the conditions in order of what needs to be met to happen for instance.
If I interpreted correctly, you are trying to:
* find stores those have sales >2 AND STR<45
* within these stores, find which store has minimum stock
for instance, there are 3 stores, in which only 2 stores have sale >2 AND STR<45, then find minimum stock among them.
Try :
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Please Login or Register to view this content.
Quang PT
1. for AL, stock has to be more than 3 AND STR more than 90, else the result is straightaway false
2. the store where transfer need to be done, has to have all the conditions:
sale > 3,
STR <45
and STR lowest among all the three (if any of the stores doesn't fall in the above criteria, must be left out from the lowest STR criteria). I hope I made my conditions clear. Thanks all of you for taking out your time.
your formula looks interesting but doesn't fulfill my job. Please read my above post for the conditions again and see if you can find the solution.If I interpreted correctly, you are trying to:
* find stores those have sales >2 AND STR<45
* within these stores, find which store has minimum stock
for instance, there are 3 stores, in which only 2 stores have sale >2 AND STR<45, then find minimum stock among them.
Try :
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.Please Login or Register to view this content.
this looks like it works...
=IF(AND(C5<=3,D5<=90),"",IF(AND(MIN(G5,I5,K5)=G5,MIN(G5,I5,K5)<45,F5>2),F3,IF(AND(MIN(G5,I5,K5)=I5,MIN(G5,I5,K5)<45,H5>2),H3,IF(AND(MIN(G5,I5,K5)<45,J5>2),J3,IF(AND(MAX(G5,I5,K5)=G5,MAX(G5,I5,K5)<45,F5>2),F3,IF(AND(MAX(G5,I5,K5)=I5,MAX(G5,I5,K5)<45,H5>2),H3,IF(AND(MAX(G5,I5,K5)<45,J5>2),J3)))))))
ARRAY formula is used.Pl see note below.
Pl see file.
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
ARRAY formula is used.Pl see note below.
Pl see file.
Please Login or Register to view this content.
done. thanks, that solves the issue.
Last edited by brajesh.sharma87; 07-14-2018 at 04:14 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks