Formula Help

[Carl_61]

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 instance, the statement in red below says: “This Is LHS” if the statement is TRUE however if FALSE I want it to say: “This Is Not LHS. Temp is below 85 degrees”.  The thing is though, I want the rest of the Nested IF Statement to result as stated at the end of the Statement: “This Is Not LHS”.

Can I present separate messages within the statement if the result of the IF Statement is False and could someone modify or help me with my statement to show me how to properly construct the IF Statement?

=IF(AND(AND(E8="Appliance",F8="Oven/Range",J8="NW")),"This Is LHS"IF(AND(AND(E8="HVAC",F8="AC",J8>85)),"This Is LHS",IF(AND(AND(E8="Appliance",F8="Dishwasher",J8="NW")),"This Is LHS",IF(AND(E8="Environmental",F8="Mold"),"This Is LHS",IF(AND(E8="Electrical",F8="No Power"),"This Is LHS",IF(AND(AND(E8="Electrical",F8="Lights",J8="NP")),"This Is LHS","This Is Not LHS"))))))

Below is what I am trying to accomplish but I cannot seem to get the Construct correct.  HELP!!!

=IF(AND(AND(E8="Appliance",F8="Oven/Range",J8="NW")),"This Is LHS",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”,IF(AND(AND(E8="Appliance",F8="Dishwasher",J8="NW")),"This Is LHS",IF(AND(E8="Environmental",F8="Mold"),"This Is LHS",IF(AND(E8="Electrical",F8="No Power"),"This Is LHS",IF(AND(AND(E8="Electrical",F8="Lights",J8="NP")),"This Is LHS","This Is Not LHS"))))))

Thank you,

Carl

[#2]

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.

[#3]

Hello Yea_so,
I replied to the completeness of the truth table already. Please review the postings and you’ll see my reply.

[#4]

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

[#5]

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

[#6]

@Carl_61 

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

[#7]

@Carl_61 

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?

[#8]

@Carl_61 

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?

[#9]

@Carl_61 

Here's your formula:

=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"
                         )
       )
    ),
     "LHS",
     "NOT LHS"
  )

cheers

[#10]

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

[#11]

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

[#12]

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

[#13 · Accepted solution]

@Carl_61 

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

[#14]

Thank you for the final formula. I applied it and it applied just fine. The problem is, its producing blank cells. I drug the formula down the cells and it blanked out all cells. What could be causing this? Also, does this new formula account for all he possible "LHS"'s and "Not LHS"'s that could a product of all the other conditions that could exist from the code above?

Thank you

[#15]

@Carl_61 

screen shot please