IF and some blank cells

[felipe_]

Hi! First of all, I'm a total noob with Excel, no shame on that. Sorry, not sorry.

So, I have some homework and I've been having problems with this:

=IF(OR(AND(F2="Fever",G2="Cough"),F2="Bronquitis",F2="Ear Pain",F2="Sore throat"),"Cold",(there's more here but apparently there aren't issues with this),"")

I was told that if F2 has Fever and G2 Cough, or F2 has Ear Pain or Sore Throat the "diagnostic" should be "Cold", but since not every cell has something in "G" is giving me the green rectangle. If I type something it disappears, but I guess I need to add something to tell the formula to ignore if the cell is blank.

Please help me

 

[jtakw]

Hi,

Thanks for letting us know it's homework, I'll be happy to give you some guidance if I can.

From what I can see, if G2 is Empty (Blank), your formula shouldn't give you any "error" indications, it should probably just give you a "Blank" result in your formula cell (as you have it in your formula for "value if false") indicated below in Red.

Also, the part in brackets in your formula (indicated in Blue below) probably shouldn't be there (either this part or the red part don't belong), you would end up with "too many arguments" for the IF formula (unless that's another Nested IF).

=IF(OR(AND(F2="Fever",G2="Cough"),F2="Bronquitis",F2="Ear Pain",F2="Sore throat"),"Cold",(there's more here but apparently there aren't issues with this),"")

If you provide the full requirements of the assignment, then we can try and sort this out.

 

[felipe_]

Thank jtakw!

The requirements are:
Column F says "Main symptom" and G is "Other Symptoms"
-If Main Symptoms says "Fever" and Other Symptoms "Cough", or Main Symptoms has Ear Pain, Bronquitis or Sore Throat the "diagnostic" should be "Cold"
-If the MS is "Stomach ache" or "Colitis", the diagnostic is "Gastritis"
-If the MS is "Asthma" or "Allergies" then is "Respiratory Allergies"
-Otherwise a blank cell

My formula:
=IF(OR(AND(F2="Fever",G2="Cough"),F2="Bronquitis",F2="Ear Pain",F2="Sore throat"),"Cold",IF(OR(F2="Stomach ache",F2="Colitis"),"Gastritis",IF(OR(F2="Asthma",F2="Allergies"),"Respiratory allergies","")))

And the ones without "Cough" have the green triangle since G is empty. If I trace the error it points to the blank cell, but it gives the right "diagnostic" anyway.

 

[jtakw]

Great job, your formula is Exactly on point with the requirements you described.

I can't replicate the "green triangle error" you're describing, and your formula is correct and should Not error out at all.

As far as G2 being Blank or Anything other than "Cough", then the first part of your formula (in red) will just evaluate to a logical FALSE, and will jump to the Next IF logical test (in blue).

=IF(OR(AND(F2="Fever",G2="Cough"),F2="Bronquitis",F2="Ear Pain",F2="Sore throat"),"Cold",IF(OR(F2="Stomach ache",F2="Colitis"),"Gastritis",IF(OR(F2="Asthma",F2="Allergies"),"Respiratory allergies","")))

I don't see Any problems with your formula at all, it's working exactly as it should according to your description, and there's No need to add a check to see if G2 is Blank.

 

[felipe_]

Is there any rule for the order of AND/OR? Cause someone is telling me that AND should always be before OR, but I tried switching the order with its contents and it gives me an error.

 

[jtakw]

No, there's Absolutely no such rule, that "someone" is mistaken, the placement of AND/OR, OR/AND, or even multiples of one or Both All depends on the logic you want the formula to follow.

As the way you have set up your formula, you understand the logic of using the OR/AND correctly for the formula to perform correctly according to your requirements.

Here's a simple sample of an AND/OR statement, I'm sure you can follow the logic:

Book1
	A	B	C
1	Dog	Domesticated	Pet
Sheet133

Cell Formulas
Range	Formula
C1	=IF(AND(OR(A1="Dog",A1="Cat"),OR(B1="Domesticated",B1="Obedient")),"Pet","Wild")