IF Formula with AND and Between help please

[Combat Womble]

Hello.
Thank you for the help you have given me over the years - this site is my number excel reference! This time, I just can't figure it out I am trying to get right a small part of what will be a bigger formula.

Can you tell me whats wrong with this: Excel gives me an error message about "
=IF((AND, M2="y", R2<=.26),(L2-1.657+B2)*$V$1*1.25,"TEST")

If M = y AND R = .26 or below then [a bit of math]. If not print test.

I need the principle to work so I can create a big IF formula.

That will look something like

=IF((AND, M1=”y”, R1<=.26),[Math], IF((AND, M1=”y”, (AND, R1>=.261, R1<=.28),[Math], IF((AND, M1="y", R1>=.281),[Math], IF((AND, M1=”n”, R1<=.26),[Math], IF((AND, M1=”n”, (AND, R1>=.261, R1<=.28),[Math], IF((AND, M1="n", R1>=.281),[Math],”ERROR”))))))))))

... but have more permutantions.

<tbody>
[TD="class: xl65"] y
[/TD]
[TD="class: xl65, width: 79"] under.19 [/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 27"] n [/TD]
[TD="class: xl65, width: 80"] under.19 [/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"] between [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"] between [/TD]

[TD="class: xl65"] y [/TD]
[TD="class: xl65"] .20 and .23 [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"] n [/TD]
[TD="class: xl65"] .20 and .23 [/TD]

[TD="class: xl65"] y [/TD]
[TD="class: xl65"] .23 and .259 [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"] n [/TD]
[TD="class: xl65"] .23 and .259 [/TD]

[TD="class: xl65"] y [/TD]
[TD="class: xl65"] .26 and .289 [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"] n [/TD]
[TD="class: xl65"] .26 and .289 [/TD]

[TD="class: xl65"] y [/TD]
[TD="class: xl65"] .29 and .319 [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"] n [/TD]
[TD="class: xl65"] .29 and .319 [/TD]

[TD="class: xl65"] y [/TD]
[TD="class: xl65"] .32 and .349 [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"] n [/TD]
[TD="class: xl65"] .32 and .349 [/TD]

[TD="class: xl65"] y [/TD]
[TD="class: xl65"] .35 and .379 [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"] n [/TD]
[TD="class: xl65"] .35 and .379 [/TD]

[TD="class: xl65"] y [/TD]
[TD="class: xl65"] .38 and .409 [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"] n [/TD]
[TD="class: xl65"] .38 and .409 [/TD]

[TD="class: xl65"] y [/TD]
[TD="class: xl65"] .41 and .439 [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"] n [/TD]
[TD="class: xl65"] .41 and .439 [/TD]

[TD="class: xl65"] y [/TD]
[TD="class: xl65"] over.4 [/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"] n [/TD]
[TD="class: xl65"] over.4 [/TD]

</tbody>

I have about 3.5k lines of data.

Please can you tell me what I've done wrong with the above formula. Also is there an easy way to do this? (intead of writing a very large IF formula)

 

[Eric W]

Try:

=IF(AND(M2="y", R2<=0.26),(L2-1.657+B2)*$V$1*1.25,"TEST")


Incidentally, you should probably use some form of a LOOKUP or MATCH to go through a table like that. The resulting formula will be much simpler and easier to manage than a large nested IF.

 

[Combat Womble]

Try:

=IF(AND(M2="y", R2<=0.26),(L2-1.657+B2)*$V$1*1.25,"TEST")

Incidentally, you should probably use some form of a LOOKUP or MATCH to go through a table like that. The resulting formula will be much simpler and easier to manage than a large nested IF.

Thank you! (happy, yet annoyed, I was so close).

I am off to try and learn basics of LOOKUP and MATCH. Time for me to move on from large IF formulas.

Thank you again.