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
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
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.
- 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 ??- 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