IF, AND, and OR Formula #VALUE error

[psuedodragon]

Background: I'm working on a constructed language, and wanting to use an Excel formula to check that the rules are being followed as words are created.

So the first rule I'm trying to check is that the first letter of a word, depending on the type of word (Noun, Verb, Adjective, Adverb, and Preposition) is one of a specific set of letters.

Namely, if it's a Noun, then the first letter must be W or E
If it's a Verb, then the first letter must be a C or V
If it's an Adjective, then the first letter must be an O or U
If it's an Adverb, then the first letter must be an S or M
If it's a Preposition, then the first letter must be a Y, H, A, F, N, or T

So the formula I was trying to use was:
=IF(AND(B2="Noun",C2=OR("W","E")),"Good",IF(AND(B2="Verb",C2=OR("C","V")),"Good",IF(AND(B2="Adjective",C2=OR("O","U")),"Good",IF(AND(B2="Adverb",C2=OR("S","M")),"Good",IF(AND(B2="Preposition",C2=OR("Y","H","A","F","N","T")),"Good","Error")))))

But regardless of what values are in any of the squares, it returns a #VALUE! error.

When I click through to evaluate, its telling me the problem is with my OR function.

Suggestions or ideas?

 

[Snakehips]

psuedodragon,

Take a look at the way you have expressed the OR statements.

Eg typically, the first one should read.... =IF(AND(B2="Noun",OR(C2="W",C2="E")),"Good",......

Hope that helps.

 

[gaz_chops]

Change your OR parts to be

or(C2="W",C2="E") etc

 

[psuedodragon]

Thanks so much! That fixes it right up

 

[skywriter]

It looks like your Or will work with this format also.

=IF(AND(B2="Noun",OR(C2={"W","E"})),"Good","Bad")


=IF(AND(B2="Noun",OR(C2={"W","E"})),"Good",IF(AND(B2="Verb",OR(C2={"C","V"})),"Good",IF(AND(B2="Adjective",OR(C2={"O","U"})),"Good",IF(AND(B2="Adverb",OR(C2={"S","M"})),"Good",IF(AND(B2="Preposition",OR(C2={"Y","H","A","F","N","T"})),"Good","Error")))))