Forum Discussion
Formula Help
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
56 Replies
Before I go away for the week, Carl, I wanted to add just one more word of encouragement to you here. You're clearly involved in facility maintenance in some way, so I'm sure you are well acquainted with the value of preventing problems in the first place. It's very much the case that fixing things can be--often if not usually--far more expensive than preventing the problem in the first place. We have all kinds of folk sayings about this truism:
- An ounce of prevention is worth a pound of cure...
- Don't wait to close the barn door until after the horse has run... (multiple variations on that one)
All of our suggestions are not meant to deny the need you have, and I hope we can get it done. I do hope you see this attempt, though, as comparable to closing the barn door after the damage (the ambiguity, in this case) has been done.
You (your organization) would be better served by fixing the processes by which these word orders are created in the first place, so that LHS situations are red-flagged right at the start, as they're written up. Whoever the software vendor is that created the basic system should be eager to make it more effective, for you as well as for other clients.
- mathetes
JMB17
Thank you John for your words of wisdom and encouragement. My job is one of oversight of the company that actually manages the properties. We do not monitor the work orders as they come into the managing company, we pour thru reports and such on a monthly basis to see how they are doing and report to them what Work Orders were deemed LHS. As you have seen on the Work Order Example I sent, there is a column, Column E, that specifies Priority. One of our jobs is to Monitor the applied Priority and to determine if the Work Order was or was not considered an LHS Work Order. So, this being said, you can see that our roll sits outside the day to day workings of the managing company. You could say we are Performance Evaluation Company meant to monitor various aspects of the managing company's operation. We review reports, have meetings with and report to the managing company how they are doing according to their governing documents. This is why as I have stated we don't need PERFECT, we just need a way to blast thru the numerous amounts of Work Orders looking for key words that would give us an idea of what Work Orders may be considered LHS. But yes, we realize we will still have go thru the Work Order reports manually to verify otherwise. I am not apposed to a VBA Solution or an applied Formula to each report as we get it. I am just looking for a solution that will help me zip thru the report to give me an initial heads up on potential LHS issues. I really like the idea of the Table Solution you presented to me initially as its something we could build on to better improve the results. The idea is to TAG each Work Order with LHS or NLHS by inserting a column at the beginning or end of the report. The assignment of the TAG, LHS or NLHS may not always be correct but that's ok. Like I said, I'm not looking for PERFECT, just best guess. I would hope we could see this thru so I end up with some kind of Solution. Thank you and any other contributor's for helping me with this.Give this file a try. Put the reports you want to process in their own folder, then click the button on the worksheet, which will open a dialog box for you to select your folder. Then, it should go through the files and find the category/subcategory/description/LHS fields (inserting a column for the LHS field if it does not find it as I assume that is not a field that is standard for the report - also, I'm assuming the other field header names are standard). And then, going through the descriptions, plugging them into the worksheet, getting the formula result and putting LHS or NLHS in the LHS field. Then, saving the file (which could be changed to a SaveAs if you don't want to overwrite the existing file).
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.
- 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?
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.
- 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?
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.
- 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.
- 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?
