Jan 30 2022 02:56 PM
I am building an if statement that requires a look into a cell "G4" to see if the cell contains "85F or more Inside" that the cell would display "LHS" in it and if not it would display "Not LHS".
=IF(AND(AND(E4="APPLIANCE",F4="OVEN/RANGE")),IF(OR(ISNUMBER(SEARCH({"*GAS*","*Hissing*","*Leaking*"},G4))),"LHS", "Not LHS"),IF(AND(AND(E4="APPLIANCE",F4="DISHWASHER")),IF(OR(ISNUMBER(SEARCH({"*LEAK*","*NOT WORKING*"},G4))),"LHS","NOT LHS"),IF(AND(AND(E4="HVAC",F4="AC")),IF(OR(ISNUMBER(SEARCH({"*LEAK*","*NOT BLOWING*"},G4))),"LHS","NOT LHS"),IF(AND(AND(E4="HVAC",F4="AC")),IF(OR(ISNUMBER(SEARCH({">85F Inside","*NOT WORKING*"},G4))),"LHS","NOT LHS")))))
I am having trouble figuring this out. Any help would be appreciated.
Thank you,
Carl
Jan 30 2022 03:45 PM
Carl, let's assume you do figure it out, and then pass on the spreadsheet to somebody else, and the time comes when that somebody else needs to make a modification. Do you think they'd be able to figure it out?
What I'm getting at is that this formula is almost a case-study in formulas that should not be written as a single formula. Most text books on Excel advise specifically avoiding deeply nested IF functions for precisely the reason that you're encountering: they're hard to create in the first place, even harder to decipher.
The help I'd offer is this: break the various test conditions apart and deal with such things as different appliances separately. Don't try to write a single master function that covers the universe of different problems or conditions that could arise.
Another alternative is to create a table of conditions and use XLOOKUP or INDEX and MATCH to find the right combination of conditions.
What's the array of possible data that will appear in cells E4, F4, and G4. What cell is this formula itself in, for that matter? You don't explain what LHS is, for another thing, but these are just examples of how hard you're making it for anybody on the outside to help. If you can spell out in simple words what you're trying to do, the bigger picture, with an example or two in words, maybe somebody here could help.
Jan 30 2022 04:38 PM
Jan 30 2022 05:05 PM
Jan 30 2022 05:36 PM
Jan 30 2022 09:47 PM
You might try putting your LHS conditions into a table and then using a formula to see if the values in E4, F4, and G4 exist in the table. Then, adding additional entries is just a matter of adding items to the table. Here is a description of how the formula is being used for a multiple condition test, so hopefully you will be able to follow it:
http://xldynamic.com/source/xld.SUMPRODUCT.html
As @mathetes has also already said, this is not ideal. Ideally, whatever application or software you are using on the front end would have data validation so that the input was standard or would have some more reliable means of identifying LHS situations. But, if that's not an option, perhaps this will be somewhat helpful.
Jan 31 2022 07:35 AM
@JMB17 gave us a great start with his LHS spreadsheet.
I've extended the table and added what I call a "cascading data validation system" in the attached. This does require the most current version of Excel because it makes extensive use of the FILTER and UNIQUE functions.
So @Carl_61 there's now a simple formula that identifies which conditions are LHS based on the table at the far left of the sheet.
Jan 31 2022 08:56 AM
Jan 31 2022 09:05 AM
Jan 31 2022 09:19 AM
Jan 31 2022 09:29 AM
Jan 31 2022 09:59 AM
Jan 31 2022 10:23 AM
First, Carl, you don't need to send each of us a reply separately. You can reply to multiple people here with one message, as I've done above. Just type the "@" symbol and the website itself will supply a list of others who are in the same thread.
Anyway, back to the issue at hand. Let me ask, since you can't change the raw data entry process, at which point does all this get put into Excel? How does that happen?
It just seems that worst case, you still would be better off NOT trying to come up with your own artificial intelligence formula that would take random text entries and make sense of them....maybe you need to hire a separate person to go down that long list and, using the table that @JMB17 and I have given you, create a clean set of data points on each row, with a reliable formula, such as we've given you, to identify the priority items.
In the absence of that, you (or somebody) will still need to go through the list manually to make sure that the incomplete and unreliable formula --which is the best you and we will be able to do anyway -- hasn't missed anything. Basically, it's the process itself that needs to be made reliable.
Jan 31 2022 11:51 AM
Jan 31 2022 12:31 PM
Jan 31 2022 12:57 PM
Jan 31 2022 01:00 PM
Jan 31 2022 01:51 PM
@Carl_61 Carl: I don't know about @JMB17 , but I can't help at all, beyond what I've already done, without seeing an example of what you're looking at. That is, seeing the actual Excel file. Not just an image; an actual Excel file. You should be able to attach a copy of it here, so long as it includes no proprietary or confidential info. If it does--e.g., names of residences, then delete those fields.
Even with that, though, I'm not all that confident that I could suggest something. As I said in my last posting, I think the very process that generates these needs to be adjusted so that there is less in the way of random entries. I would not be at all confident--NOT AT ALL--of any system using SEARCH or anything comparable to run through and look for key words. There are simply too many ways for it to get messed up. As you put it yourself, "Let's just say some people are not the greatest at explaining what is going on with their situation." So like it or not, you have to go through the list one-by-one to ensure you've not missed anything. In my opinion. (By the way, I live in a five-building apartment complex in Manhattan; for all I know, you could be describing exactly what happens here in our buildings.....except we don't report appliances to management, only HVAC stuff; we're on our own for our appliances)
Jan 31 2022 02:16 PM
Jan 31 2022 02:56 PM
Here is a copy of a Work Order Report. The Columns in Yellow are the columns we are using to determine LHS issues. The column in green is my attempt to visually determine if it is LHS or NLHS. I manually inserted LHS and NLHS. If this gives you more insight you can then help me understand the table idea.