Forum Discussion
Formula Help
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.
- 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?- 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.