Sep 30 2021 01:44 PM
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
Sep 30 2021 02:26 PM
I need to find out what the context of this statement is:
AND(AND(E8="Appliance",F8="Oven/Range",J8="NW"))
E8 is either "Appliance" or "Electrical"
so
"Appliance"= True?
"Electrical"= False?
Lets clarify that first
Sep 30 2021 02:33 PM
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.
Sep 30 2021 02:47 PM
Sep 30 2021 02:57 PM
Sep 30 2021 03:02 PM
Sep 30 2021 04:03 PM - edited Sep 30 2021 06:34 PM
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
Oct 05 2021 12:01 PM
Oct 05 2021 01:26 PM
Oct 05 2021 01:32 PM
Oct 05 2021 01:42 PM
Oct 05 2021 01:46 PM
Oct 06 2021 02:25 PM
Oct 06 2021 02:47 PM
Oct 06 2021 03:18 PM
Ok so let's define every detail to each Main category which is in column E:, subcategory which is in Column F, and Statuses, which is in column J
We need to create a truth table to evaluate each scenario (which is what the formula does) and the formula needs these instructions
(computers are pretty dumb because they can't make assumptions based on implications)
we have to tell it like it is at every scenario (even though it is painful and passes that pain to the solution developer and propagates that pain to the poster
Please send some more of this info:
Appliance = Dehumidifier, Dishwasher, Oven/Range, Water Heater
Dehumidifier can be: Broken, Leaking, No Power, Noisy
Dishwasher can be: Broken, Leaking, No Power, Not Heating, Noisy
Oven/Range can be: Broken, Gas Smell, Gas Leak, No Power, Not Heating
Water Heater can be: Broken, Gas Smell, Gas Leak, Leaking, No Power, Not Heating, Noisy
on all the other main categories. Thank you.
Cheers
Oct 06 2021 07:21 PM
Thankyou for your help with this.
My 2:25 pm Reply above was my attempt to show each Item in category 1 (column E) its connection to the items in category 2 (column F) and the possible selections available in category 3 (column J).
In that reply I Listed each Item in column E, such as Appliance and put an equal sign to the right of it showing the association from column F and then the possible selections from column J.
If you look this over carefully I believe you will see what I mean. So I hope the question you are asking in your latest response will be answered or provide you the necessary information.
Thank you,
Carl
Oct 06 2021 08:45 PM
Oct 07 2021 06:25 AM
Oct 14 2021 12:47 PM
SolutionYour 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