Forum Discussion
Formula Help
I need help from someone who knows the answer to my question below.
I have this Nested IF Statement whereby I want to when needed, provide a separate answer if the statement is False. For instance, the statement in red below says: “This Is LHS” if the statement is TRUE however if FALSE I want it to say: “This Is Not LHS. Temp is below 85 degrees”. The thing is though, I want the rest of the Nested IF Statement to result as stated at the end of the Statement: “This Is Not LHS”.
Can I present separate messages within the statement if the result of the IF Statement is False and could someone modify or help me with my statement to show me how to properly construct the IF Statement?
=IF(AND(AND(E8="Appliance",F8="Oven/Range",J8="NW")),"This Is LHS"IF(AND(AND(E8="HVAC",F8="AC",J8>85)),"This Is LHS",IF(AND(AND(E8="Appliance",F8="Dishwasher",J8="NW")),"This Is LHS",IF(AND(E8="Environmental",F8="Mold"),"This Is LHS",IF(AND(E8="Electrical",F8="No Power"),"This Is LHS",IF(AND(AND(E8="Electrical",F8="Lights",J8="NP")),"This Is LHS","This Is Not LHS"))))))
Below is what I am trying to accomplish but I cannot seem to get the Construct correct. HELP!!!
=IF(AND(AND(E8="Appliance",F8="Oven/Range",J8="NW")),"This Is LHS",IF(AND(AND(E8="HVAC",F8="AC",J8>85)),"This Is LHS. Temp Is above 85 degrees",”This Is Not LHS. Temp is Below 85 degrees”,IF(AND(AND(E8="Appliance",F8="Dishwasher",J8="NW")),"This Is LHS",IF(AND(E8="Environmental",F8="Mold"),"This Is LHS",IF(AND(E8="Electrical",F8="No Power"),"This Is LHS",IF(AND(AND(E8="Electrical",F8="Lights",J8="NP")),"This Is LHS","This Is Not LHS"))))))
Thank you,
Carl
Your Final Formula:
IFERROR(
IF(
OR(
AND(E2="HVAC",F2="AC",J2>85),
AND(E2="HVAC",OR(
F2="AC",
F2="Dehumidifier"
),
OR(
J2="No Power",
J2="Leaking",
J2="Noisy",
J2="Broken"
),
),
AND(E2="Appliance",OR(
F2="Dehumidifier",
F2="Dishwasher",
F2="Water Heater"
),
OR(
J2="No Power",
J2="Leaking",
J2="Noisy",
J2="Broken"
)
),
AND(E2="Appliance",OR(
F2="Dishwasher",
F2="Water Heater",
F2="Oven/Range"
),
J2="Not Heating"
),
AND(E2="Appliance",OR(
F2="Water Heater",
F2="Oven/Range"
),
OR(
J2="Gas Leak",
J2="Gas Smell"
)
),
AND(E2="Electrical",F2="Lights",OR(
J2="No Power",
J2="Broken"
)
),
AND(E2="Environmental",F2="Mold",OR(
J2="Present",
J2="Visible"
)
),
AND(E2="Plumbing",OR(
F2="Drain",
F2="Pipes",
),
OR(
J2="Leaking",
J2="Smell",
J2="Clogged",
J2="Noisy",
J2="Broken"
)
),
AND(E2="Structural",F2="Ceiling",OR(
J2="Leaking",
J2="Broken"
)
),
AND(E2="Structural",F2="Mold",OR(
J2="Present",
J2="Visible"
)
)
),
IFS(AND(E2="HVAC",F2="AC",AND(ISNUMBER(J2),J2>85)),
"LHS Temp is 86 and above",
AND(E2="HVAC",F2="AC",J2<=85),
"This is Not LHS Temp is 85 and below ",
AND(E2<>"HVAC",F2<>"AC",NOT(ISNUMBER(J2))),"LHS"
),
IFS(AND(E2="HVAC",F2="AC",J2>85),
"LHS Temp is 86 and above",
AND(E2="HVAC",F2="AC",J2<=85),
"This is Not LHS Temp is 85 and below ",
AND(E2<>"HVAC",F2<>"AC",NOT(ISNUMBER(J2))),"NOT LHS"
)
),
"")cheers
33 Replies
Your Final Formula:
IFERROR(
IF(
OR(
AND(E2="HVAC",F2="AC",J2>85),
AND(E2="HVAC",OR(
F2="AC",
F2="Dehumidifier"
),
OR(
J2="No Power",
J2="Leaking",
J2="Noisy",
J2="Broken"
),
),
AND(E2="Appliance",OR(
F2="Dehumidifier",
F2="Dishwasher",
F2="Water Heater"
),
OR(
J2="No Power",
J2="Leaking",
J2="Noisy",
J2="Broken"
)
),
AND(E2="Appliance",OR(
F2="Dishwasher",
F2="Water Heater",
F2="Oven/Range"
),
J2="Not Heating"
),
AND(E2="Appliance",OR(
F2="Water Heater",
F2="Oven/Range"
),
OR(
J2="Gas Leak",
J2="Gas Smell"
)
),
AND(E2="Electrical",F2="Lights",OR(
J2="No Power",
J2="Broken"
)
),
AND(E2="Environmental",F2="Mold",OR(
J2="Present",
J2="Visible"
)
),
AND(E2="Plumbing",OR(
F2="Drain",
F2="Pipes",
),
OR(
J2="Leaking",
J2="Smell",
J2="Clogged",
J2="Noisy",
J2="Broken"
)
),
AND(E2="Structural",F2="Ceiling",OR(
J2="Leaking",
J2="Broken"
)
),
AND(E2="Structural",F2="Mold",OR(
J2="Present",
J2="Visible"
)
)
),
IFS(AND(E2="HVAC",F2="AC",AND(ISNUMBER(J2),J2>85)),
"LHS Temp is 86 and above",
AND(E2="HVAC",F2="AC",J2<=85),
"This is Not LHS Temp is 85 and below ",
AND(E2<>"HVAC",F2<>"AC",NOT(ISNUMBER(J2))),"LHS"
),
IFS(AND(E2="HVAC",F2="AC",J2>85),
"LHS Temp is 86 and above",
AND(E2="HVAC",F2="AC",J2<=85),
"This is Not LHS Temp is 85 and below ",
AND(E2<>"HVAC",F2<>"AC",NOT(ISNUMBER(J2))),"NOT LHS"
)
),
"")cheers
- Thank you for the final formula. I applied it and it applied just fine. The problem is, its producing blank cells. I drug the formula down the cells and it blanked out all cells. What could be causing this? Also, does this new formula account for all he possible "LHS"'s and "Not LHS"'s that could a product of all the other conditions that could exist from the code above?
Thank you
Here's your formula:
=IF(
OR(
AND(E2="HVAC",F2="AC",J2>85),
AND(E2="HVAC",OR(
F2="AC",
F2="Dehumidifier"
),
OR(
J2="No Power",
J2="Leaking",
J2="Noisy",
J2="Broken"
),
),
AND(E2="Appliance",OR(
F2="Dehumidifier",
F2="Dishwasher",
F2="Water Heater"
),
OR(
J2="No Power",
J2="Leaking",
J2="Noisy",
J2="Broken"
)
),
AND(E2="Appliance",OR(
F2="Dishwasher",
F2="Water Heater",
F2="Oven/Range"
),
J2="Not Heating"
),
AND(E2="Appliance",OR(
F2="Water Heater",
F2="Oven/Range"
),
OR(
J2="Gas Leak",
J2="Gas Smell"
)
),
AND(E2="Electrical",F2="Lights",
OR(
J2="No Power",
J2="Broken"
)
),
AND(E2="Environmental",F2="Mold",
OR(
J2="Present",
J2="Visible"
)
),
AND(E2="Plumbing",OR(
F2="Drain",
F2="Pipes",
),
OR(
J2="Leaking",
J2="Smell",
J2="Clogged",
J2="Noisy",
J2="Broken"
)
),
AND(E2="Structural",F2="Ceiling",
OR(
J2="Leaking",
J2="Broken"
)
),
AND(E2="Structural",F2="Mold",
OR(
J2="Present",
J2="Visible"
)
)
),
"LHS",
"NOT LHS"
)cheers
- Sorry I wasn’t able to respond to you sooner but this looks like it may just work. The only issue I see is that the first statement should have a separate message regarding J2 being greater than 85. “LHS, Temp over 85”,”Not LHS, Temp below 85”. Otherwise it looks good but I’ll apply it tomorrow to see how it works. Thank you
- The formula works, that is no errors or any thing like that. I still have to put it to practical application but for what you gave me it appears to work fine. I posted another question about something different but if you had a moment to reply it would be greatly appreciated.
I am trying to determine the function or formula for finding specific text on a sheet and when found to return the Cell reference number where it was found. Such as: find "Apple" in one of the cells and return the Cell Reference the text was found in. Such as: C10 if "Apple" was found in that cell. Apple is not what I am looking for but I'm just using that as a example
Can you help me with as well.
Thank you,
Carl
- I have just noticed that there appears to be an issue with excel or a setting regarding calculations.
I have "NW" in cell J2 fore instance and when I create a basic IF statement I get a result I cannot figure out. Here's the statement:
IF(J2="NW","Yes","No") I get "Yes" if NW is in that cell.
IF(J2=85,"Yes","No") I get "Yes" if 85 is in the cell
IF(J2>85, "Yes","No") I get "Yes" even though "NW" is in the cell. I would expect a "NO" as "NW" is in that cell.
Anyone have any ideas about this?- the syntax for IF():
IF([Condition],[ACTION IF TRUE],[ACTION IF FALSE])
Condition can be the value in the cell is "NW" then its true
Condition can be value in cell is >n or <n
Condition can be ISBLANK(J2) or NOT(ISBLANK(J2)) etc....- I am still perplexed regarding the IF Statement issue I mentioned. This is because no matter what Cell I place the formula, in my eyes, I am still getting an invalid result. =IF(F5="NW","Yes","No") I am expecting a "Yes" if I know "NW" is in that cell. The same with =IF(F5=85,"Yes","No") I am expecting a "Yes" if I know 85 is in that cell. So when I have =IF(F5>85,"Yes","No") I am expecting a "Yes" if F5 has 86 in the cell and I am expecting a "No" if less than 85 or if "NW" is in that cell I am also expecting a "No" as the statement should evaluate to "Not True". Why will the formula result in "Yes" if F5 = 85 and result to "No" if the cell contains "NW". Change it to F5>85 it returns a "Yes". I simply do not understand.
Personally, I'd recommend an altogether different approach. If you can create a table with the various combinations of possibilities and the message that should be appearing, the relatively new FILTER function can work, as the attached shows. I think I faithfully copied all your conditions and the right responses.....
FILTER does require the most recent version of Excel, however.
But a more fundamental point is that deep nested IFs (as you're experiencing) are very difficult to create in the first place, hard to maintain (if new conditions arise or the maintenance gets passed to someone else). A table is a far better solution over time and FILTER allows you to specify multiple conditions.
- Thank you for your suggestion. I'll check it out when I get home.
- My formula works if I leave thing alone as shown in my first example. I am only trying to adjust the statement that is in RED. The True part of the statement works and allows me to change it to what ever I want as long as its in the quote marks. I am wanting to incorporate the IF False part of that part of the IF Statement within the IF Statement shown in RED below the example I have posted. When Excel evaluates the nested IF and it sees
IF(AND(AND(E8="HVAC",F8="AC",J8>85)),"This Is LHS. Temp Is above 85 degrees",”This Is Not LHS. Temp is Below 85 degrees”
I want the evaluation to be obtained as shown instead of getting the FALSE result at the end of the nested IF. Is this possible and if so, what adjustment(s) do I have to make to make this work.Try this:
=IF(E8="HVAC",
IF(AND(E8="HVAC",F8="AC",J8>85),
"This Is LHS. Temp Is above 85 degrees",
"This Is Not LHS. Temp is Below 85 degrees"
),
IF(
OR(
AND(E8="Environmental",F8="Mold"),
AND(E8="Electrical",F8="No Power"),
AND(E8="Electrical",F8="Lights",J8="NP"),
AND(E8="Appliance",OR(
F8="Oven/Range",
F8="Dishwasher"),
J8="NW"
),
),
"This Is LHS",
"This Is Not LHS"
)
)cheers
