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
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
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
- 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"