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
Sorry I wasn’t able to respond to you sooner but this looks like it may just work. The only issue I see is that the first statement should have a separate message regarding J2 being greater than 85. “LHS, Temp over 85”,”Not LHS, Temp below 85”. Otherwise it looks good but I’ll apply it tomorrow to see how it works. Thank you
The formula works, that is no errors or any thing like that. I still have to put it to practical application but for what you gave me it appears to work fine. I posted another question about something different but if you had a moment to reply it would be greatly appreciated.
I am trying to determine the function or formula for finding specific text on a sheet and when found to return the Cell reference number where it was found. Such as: find "Apple" in one of the cells and return the Cell Reference the text was found in. Such as: C10 if "Apple" was found in that cell. Apple is not what I am looking for but I'm just using that as a example
Can you help me with as well.
Thank you,
Carl
I am trying to determine the function or formula for finding specific text on a sheet and when found to return the Cell reference number where it was found. Such as: find "Apple" in one of the cells and return the Cell Reference the text was found in. Such as: C10 if "Apple" was found in that cell. Apple is not what I am looking for but I'm just using that as a example
Can you help me with as well.
Thank you,
Carl