Forum Discussion
Formula Help
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
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 ??
So in a nutshell, which is more efficient to the formula to have an AND() then an OR(0 nested, and which items would be more efficient to the formula to have an OR() then an AND() nested.
In other words which nest should I use for items in the 2nd column where there is less repetition.
for example:
AND(F2="Dehumidifier", F2="Dishwasher"),OR(J2="No Power",J2="Leaking",J2="Noisy", J2="Broken"))
well "Dehumidifier" does not have "Not Heating", so I have to repeat ORing the
"Dehumidifier""Dishwasher" with "Water Heater" and "Oven/Range" because there is that "Not Heating" in "Dishwasher" that is not in "Dehumidifier" but is in "Water Heater", and "Oven/Range" so that:AND(F2="Dishwasher",F2="Water Heater", F2="Oven/Range"), OR(J2="No Power", J2="Leaking", J2="Noisy", J2="Broken"))
So in this example "Dishwasher is ANDed with "Dehumidifier" (first instance) as well as "Water Heater" and "Oven/Range" (second instance). Don't get confused thinkin about it. As you can see in the above explanation I already got confused trying to illustrate to you the example.. so I have to be careful about it because the "Safety Implications" to the residents and to your job will be at stake! How many mistakes did I commit trying to explain the scenario to you? Five. Do you follow?
That's the easy part, the more complex part is which arrangement will be optimal to the formula, where there is less repetition of the nested OR(AND()) nest or should it be the AND(OR()) nest for each item that has some similar items and different items to and with then or with. Do you follow?
No I don't have anymore questions. I'm in the process of doing a ven diagram for the F Column based on the values if the J Column. Maybe you can help so it will also keep you busy thinkin and not get irate because its taking time?
In the truth table, Appliance which of the items in the 2nd column can I and with the 3rd column because the third column has exactly the same items exactly, and if the items in the third column has some similar items but not exactly then the 2nd column have to be anded with another item in the 2nd column 2x because some of the the third items are similar but not all. Do you follow? Don't get confused thinkin about it.
Appliance Dehumidifier No Power Leaking Noisy Broken Dishwasher No Power Leaking Noisy Broken Not Heating Water Heater No Power Leaking Noisy Broken Not Heating Gas Leak Gas Smell Oven/Range No Power Broken Not Heating Gas Leak Gas Smell - 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
- Hello Yea_so,
I replied to the completeness of the truth table already. Please review the postings and you’ll see my reply. - Just a reminder that column "J" can have a number in it which represents a Temperature to be evaluated. =IF(AND(AND(E2="HVAC",F2="AC",J2>85),"LHS. Temp Over 85","Not LHS. Temp Below 85")). But also, E2=HVAC & F2=AC, J2 can be any of the items listed for HVAC, AC in the Truth Table.
- The Truth Table looks good except Dehumidifier needs to be added HVAC in column "F". The dehumidifier is considered to be part of the HVAC system. For column "J" the Dehumidifier can be: Broken, No Power, Leaking, Noisy. Otherwise things are looking really good with the Truth Table.
Thankyou for your help with this.
My 2:25 pm Reply above was my attempt to show each Item in category 1 (column E) its connection to the items in category 2 (column F) and the possible selections available in category 3 (column J).
In that reply I Listed each Item in column E, such as Appliance and put an equal sign to the right of it showing the association from column F and then the possible selections from column J.
If you look this over carefully I believe you will see what I mean. So I hope the question you are asking in your latest response will be answered or provide you the necessary information.
Thank you,
Carl
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, Noisyon all the other main categories. Thank you.
Cheers
- 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