Formula Help

[Carl_61]

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

[#2]

@Carl_61 

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.

[#3]

Hello John, Thank you for replying to my post. I work with a lots of work orders that I have to review each month for which I want to have a quick way to scan thru them looking for conditions that may be deemed LHS. This is Life, Health, Safety issues. Issues that may effect living conditions of the residents. A report is produced each month of all work orders and I need a quick way to blast thru the report line by line to determine if any of the work orders that have been submitted have any issues that could be deemed LHS. You actually started to help me with this is the not so long ago past but other issues came up that were of more importance so I set this to the side. I like your approach but will it be viable for my needs. Each work order has a Work Order Category, a Sub-category and a Work Order Description. The Work Order Description is a Free Form Comment Box where the description of the problem is entered. So as you see in my ISNUMBER(SEARCH() I am trying to create a list of possible conditions/words that we will I checking for and if found in the comment box made by the residents, the system will flag it as a possibly "LHS". So based on the Work Order Category and also the Sub-category, the items to be looked for in the Work Order Description cell will be different based on these items. Not sure however how to build this using VLOOKUP but not apposed to different approach.

[#4]

Is it possible, Carl, to attach a copy of the spreadsheet with all those work orders (so long as there's no proprietary info in it, no names of residents, etc).

It sounds, though, as if you're trying to do almost the impossible in searching and finding warnings signs in a free form comment box...to be reliable that would take artificial intelligence.

Who generates these work orders? Are they entering them into a computer? Could there be dropdown boxes that cover the LHS conditions? In short, is there a way to get the input more refined/defined to begin with?

[#5]

The answer to that question is yes. All work orders are entered into a computer. There is a monthly report that comes out where by we check the report for that which is being claimed to be LHS issues. Our job is to sort thru the report look to verify or disclaim items being identified as LHS. I know there is no way to be 100% accurate on this but we want to have a way to blast thru lines of work orders with some kind of reasonable reliability. Right now we have to look thru these reports which can be hundreds of lines of work orders with our eyes and it takes up a bunch of time. Determining if a work order is LHS is subjective but we are trying to create a standard we all can rely on.

[#6]

@Carl_61 

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.

[#7]

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

[#8]

Thank you for your response. This looks like it might do the trick. The question I have however is how would this get run against the excel Work Order Report. The idea here is to get the report ad place this formula into the report at the far right of the report and then drag the formula down to the end of the report. Or maybe once this is figured out maybe the formula can be converted to VBA Code and run against the report. What's your thoughts on this?

[#9]

Thank you sir, I can say it looks good but I am not clear on what is going on here. The question is how do I apply this against my report? I get a work order report with sometimes up to 300 work orders, give or take, and I need a way to run a solution against the report whereby each work order is checked via the solution we come up with. I'm not apposed to a VBA Code solution as well. What's your thoughts on how I process this solution against the report?

[#10]

I've requested a complete list of both selectable Work Order Categories and Sub-categories from the people that create the work orders. We obviously know that there is no standardization for what is entered in the Work Order Description remarks cell so this is why we need to have a way looking for key words that may lead us to a possible "LHS" situation.

[#11]

@Carl_61 

I think what both @JMB17 and I are suggesting is that you look for a way to have the original entries themselves set up with data validation, rather than free form. Go back in the workflow to that stage.

Is that at all possible?

[#12]

No. Work orders are entered into the system when called into management and the comments are typed in how the resident speaks them. Also, residents are remotely able to enter work order requests thru an app. The only unstable entries are those which are entered into the the comments field when the work order is created. There is no way to standardize the entry of comments. The only standard is the selection of the Work Order Category and the Sub-category which gives a path for determining what possible comments could match the categories .

[#13]

@Carl_61 

@JMB17 

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.

[#14]

@JMB17
I hope I have this correct for what you just mentioned. Not sure though.
The program used is a property management program called Yardi. This program manages all aspects of the goings on of a property. We are given an oversight position of what they are doing so the program allows us to produce all kinds of reports to include Work Order Reports. The system allows us to view preprogramed reports or allows us to create this reports in Excel or PDF format. So to answer your question a monthly Work Order Report is produced in which we open in Excel or print. So with this report sitting in front of us in Excel Format we just want a way to zip thru it using this system we have been speaking of. We know it won't always be 100% but that's ok.

[#15]

@JMB17
@mathetes
So getting back to the initial way you were suggesting and knowing that there is no way to achieve 100% accuracy, how would I proceed to at least get a process going to achieve what I have set off to achieve? I know you most likely have a process in mind knowing all that is going on with what I have to work with unless you are still thinking this over. So the bottom line is that I have an excel report of multiple work orders that I need to scan thru looking for possible LHS issues. Again I know it won't be 100% as the remarks/comments cell has no standardization. Just trying to do the best I can and if I can continue to build into the table I will get better results over time. Also, what are you thinking when it comes to running the report against formula or the formula against the report?

[#16]

Let's keep in mind the list of things for each condition is not infinite. There are only so many conditions/words that could be considered to be LHS worthy. Its like saying Broken can be said in only so many ways. Not Working, Out of Order, etc., you know what I mean. I am just to get the best read thru the work order report to catch as many possible LHS issues are stated. Keep in mind also that just because words may be in the comments that would lend to the belief that the work order may be considered to be an LHS work order, there is a good chance the work order is not as it is stated. Another words, miss Categorized or the use of words in the comments are not appropriate to the work order. Let's just say some people are not the greatest at explaining what is going on with their situation.

[#17]

@JMB17
@mathetes
Let's keep in mind the list of things for each condition is not infinite. There are only so many conditions/words that could be considered to be LHS worthy. Its like saying Broken can be said in only so many ways. Not Working, Out of Order, etc., you know what I mean. I am just to get the best read thru the work order report to catch as many possible LHS issues are stated. Keep in mind also that just because words may be in the comments that would lend to the belief that the work order may be considered to be an LHS work order, there is a good chance the work order is not as it is stated. Another words, miss Categorized or the use of words in the comments are not appropriate to the work order. Let's just say some people are not the greatest at explaining what is going on with their situation.

[#18]

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

[#19]

@mathetes
@JMB17
I'm not looking for perfect and what happened with the table idea? If you would just explain to me how that would work and how I would run my report against the formula, I am sending you a copy of the Work Order Report,

[#20]

@mathetes 

@JMB17 

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.