Multiple IF statements with ranges

[Didley]

Hello everyone.

I'm trying to write a simple if statement to categorize the longitudinal center of gravity. Basically, if it falls withing (range) then give it the value "X."

Seems pretty straight-forward, except it's giveing me the #NAME error.

The code:

=IF(AND(N2 >= -1.225, N2 <= 0), “A”, IF(AND(N2>= 0.001, N2 <= 1.226), “B”, IF(AND(N2>= 1.227, N2 <= 2.454), “C”, IF(AND(N2>= 2.455, N2 <= 3.679), “D”, IF(AND(N2>= 3.68, N2 <= 4.904), “E”, IF(AND(N2>= 4.905, N2 <= 6.132), “F”, IF(AND(N2>= 6.133, N2 <= 7.358), “G”, IF(AND(N2>=7.359, N2 <= 8.583), “H”, IF(AND(N2>= 8.584, N2 <= 9.808), “J”, IF(AND(N2>= 9.809, N2 <= 11.036), “K”, IF(AND(N2>= 11.037, N2 <= 12.262), “L”, IF(AND(N2>= 12.263, N2 <= 13.487), “M”, IF(AND(N2>= 13.488, N2 <= 14.715), “N”, IF(AND(N2>= 14.716, N2 <= 15.94), “P”, IF(AND(N2>= 15.941, N2 <= 17.165), “Q”, IF(AND(N2>= 17.166, N2 <= 18.391), “R”, IF(AND(N2>= 18.392, N2 <= 19.619), “S”, IF(AND(N2>= 19.62, N2 <= 20.844), “T”, IF(AND(N2>= 20.845, N2 <= 22.069), “U”, IF(AND(N2>= 22.07, N2 <= 23.298), “V”, IF(AND(N2>= 23.299, N2 <= 24.523), “W”, IF(AND(N2>=24.524, N2 <= 25.748), “X”, "LOOK"))))))))))))))))))))))

It doesn't matter what value I put in N2, it gives me the #NAME error every time. Can anyone explain what I'm doing wrong? I would really appreciate it.

[FDibbins]

change your “A” to "A" (on all of your letters)

But you might be able to use a vlookup() table instead of that big formula?

[Didley]

[s]Not seeing the difference in your "A"s...?[/s]

OH!


I wrote it out in word. Didn't realize it would interpert Word "" differently than Excel "". That fixed it. THANK YOU!!!!

[FDibbins]

yup, I had a feeling you did

Try this instead. Create a table, using the lower of the 2 values in your AND(). Then next to each value, add your letter.
I started in A4:B8 (didnt enter them all, just a demo)
-1.225...a
0.001...b
1.227...c
2.445...d
and then used this...
=VLOOKUP(N2,A4:B8,2,1)
3.68 e

[Didley]

I'll save that for future reference. Thank you, again.

[Jonmo1]

A lookup would be MUCH better.

something like
=LOOKUP(N2,{-1.225,0.001,1.227,2.455,etc,etc,25.749},{"A","B","C","D",etc,etc,"LOOK"})

And it would be even easier to put those values into a range of cells, say A1:B26
A1:A26 = -1.225, 0.001, 1.227 etc..
B1:B26 = "A", "B", "C", etc..

=LOOKUP(N2,A1:B26)

[Didley]

I had seend the lookup function during my google-fu, but had never used it. I knew the if would do what I wanted so I just strong-armed it.

[FDibbins]

Happy to help and thanks for the feedback