Formula Help

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

Yea_So
Bronze Contributor
Oct 05, 2021
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 ??
- Carl_61
  Iron Contributor
  Oct 06, 2021
  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
  - Yea_So
    Bronze Contributor
    Oct 06, 2021
    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

Forum Discussion

Resources