Forum Discussion
Solved
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 instan...
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
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.
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
- Good after noon, was wondering if there were any additional questions you may have regarding this formula project of mine. I would like to assist you with assisting me so I can finalize this project. Thank you
- 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- 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.
