SOLVED

Formula Help

Iron Contributor

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

33 Replies

@Carl_61 

 

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

@Carl_61 

 

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.

 

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.
Oh by the way, The data in E8 is HVAC. The data in F8 is AC. And the data in J8 is an entered number by the user based on a Temperature reading when taken. So when the statement is Evaluated J8 is checked to see the number and if the number is above 85 it displays the TRUE or FALSE resulting statement. TRUE = "This Is LHS. Temp Is Above 85 degrees". FALSE = "This Is Not LHS. Temp Is Below 85 degrees"
Thank you for your suggestion. I'll check it out when I get home.

@Carl_61 

 

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

 

First of all I want to thank you for assisting me with my first posting for formula help. Having received this assistance I have discovered that there may be a different way or approach to creating the formula I am looking for. Please see below.

I have an Excel spread sheet with Columns A thru O and for the moment extends down 40 Rows.
Column’s E, F, G and J consist of Pick Lists. The Columns of most interest for my formula are Columns E, F, and J. The contents are as follows:

Column E Column F Column J
Appliance AC Broken
Electrical Ceilings Gas Smell
Environmental Dehumidifier Gas Leak
HVAC Dishwasher Leaking
Plumbing Drains No Power
Structural Lights Not Heating
Mold Noisy
No Power
Oven/Range
Pipes
Water Heater

I am wanting to write an IF/AND/OR Statement to cover the possible selections above. Another words,

IF Column “E” = one of the items shown above in Column “E” AND IF Column “F” = one of the items shown above in Column “F” AND IF Column “J” = one of the items shown above in Column “J” are all “TRUE”, display “This Is LHS” in Column “Q” otherwise IF “FALSE” display “This Is Not LHS” in Column Q.

I know some of the possible combinations don’t work with each other so they won’t be written as part of the IF Statement. Example: Appliance & Ceilings are not expected to be shown in Column E & Column F. Also, Plumbing & Lights, Plumbing & No Power, Plumbing & Oven/Range, HVAC & Dishwasher, HVAC & Lights, HVAC & Oven/Range, HVAC & Water Heater. You get the idea.

The one variation to the formula however is that the HVAC & AC combination can have a number in Column J that reflects a Temperature. Here is my example:

IF(E2="HVAC",IF(AND(AND(E2="HVAC",F2="AC",J2>85)),"LHS. Temp Above 85 Degrees","Not LHS. Temp Below 85 Degrees") otherwise IF(E2=”HVAC”,F2=”AC”,J2=”(1 of the items listed in column J above) IF “TRUE” “This Is LHS”, IF “FALSE” “This Is Not LHS”.

I am having a difficult time putting this together and therefore am reaching out again to those whom may assist me in creating the formula to make this happen.

Here is what I currently have thanks to the assistance of the Excel SME’s that chimed in already.

=IF(E2="HVAC",IF(AND(AND(E2="HVAC",F2="AC",J2>85)),"LHS. Temp Above 85 Degrees","Not LHS. Temp Below 85 Degrees"),IF(OR(AND(E2="Environmental",F2="Dehumidifier",F2="Mold"),AND(E2="Electrical",F2="No Power"),AND(E2="Electrical",F2="Lights",J2="NP"),AND(E2="Appliance",OR(F2="Oven/Range",F2="Dishwasher"),J2="NW"),),"This is LHS","This Is Not LHS"))

Thank you in advance and it would be greatly appreciated to receive assistance with this formula.

Carl

@Carl_61 

Perhaps like this

image.png

@Carl_61 

 

Is this correct?:

Yea_So_0-1633465307967.png

 

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?
Yes except "No Power" in column "J" actually belongs in Column "F" in the blank space as its one of the items in that pick list. Yes I know it should be in Column "J" as well. So it will be in both locations.
oven range belongs to appliance?
and water heater belongs to plumbing? or HVAC?
and why is noisy in column J with mold in column F?
and pipes definitely belong to plumbing unless ??
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....
Column "E" consists of 6 items.
Appliance, Electrical, Environmental, HVAC, Plumbing & Structural. These are the items on the pick list.
Column "F" Consists of 11 Items which are:
AC, Ceilings, Dehumidifier, Dishwasher, Drains, Lights, Mold, No Power, Oven/Range, Pipes & Water Heater.
Column "J" Consists of 11 Items which are:
Broken, No Power, Leaking, Not Heating, Noisy, Gas Smell, Gas Leak, Clogged, Smell, Present, Visible
Each Cell in a Row can have any variation of these items but they do not all match up or make sense to be matched up with each other. So looking at the list of items across the cells here are the match-able items:
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
These are the associations of Columns E, F & J.

PS. I Added "Clogged", "Smell", "Present" & "Visible" into Column J
Electrical = Lights, No Power - Lights can be: Broken, No Power
Environmental = Mold - Present, Visible
HVAC = AC, Dehumidifier - AC can be: Broken, No Power, Leaking, Noisy
Dehumidifier can be: Broken, No Power, Leaking, Noisy
Plumbing = Drains, Pipes - Drains can be: Broken, Leaking, Clogged, Smell
Pipes can be: Broken, Leaking, Noisy
Structural = Ceilings, Mold - Ceilings can be: Broken, Leaking. Mold can be Visible, Present
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.

@Carl_61 

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

@Yea_So 

 

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

@Carl_61 

Please confirm the completeness of the Truth table:

Yea_So_0-1633578286832.png

 

Thank you.

 

cheers

The Truth Table looks good except Dehumidifier needs to be added HVAC in column "F". The dehumidifier is considered to be part of the HVAC system. For column "J" the Dehumidifier can be: Broken, No Power, Leaking, Noisy. Otherwise things are looking really good with the Truth Table.
1 best response

Accepted Solutions
best response confirmed by Carl_61 (Iron Contributor)
Solution

@Carl_61 

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

View solution in original post