Nested IF Function not working

[Paula]

This is the formula I put in:
=if(L42<3,"$50",if(L42>2<5,"$100")). Instead of returning $50 or $100 it
returns FALSE. What am I doing wrong?

Thank You.

 

[Gary Brown]

The 2nd If( ) function has some issues.
I THINK you mean...
=if(L42<3,"$50",if(and(L42>2,L42<5),"$100","$0"))
--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown

 

[Mike H]

Paula

=IF(L42<3,"$50",IF(AND(L42>2,L42<5),"$100",""))

Mike

 

[Dave Peterson]

One more:

=IF(L42<3,50,IF(L42<5,100,""))

I don't see a reason to check for > 2 and < 5 in that second if().

And I changed the text to real numbers.

 

[Paula]

I put in this, the number in K5 is 10, so according to this formula should
return $260, but will not return anything. What ever I put in the last
quotation marks, that is what it returns. Any suggestions? The actual
formula will have 11 parts, where this example only has 4 parts.

=IF(K5<3,"$50",IF(AND(K5>2<6),"$100",IF(AND(K5>5<9),"$180",IF(AND(K5>8<13),"$260",""))))

 

[Rick Rothstein \(MVP - VB\)]

Take a look at how Mike constructed his AND function call, then look at how
you did it... Mike's construction is correct syntax, yours is not. And don't
forget to look at Dave's response in case you really wanted the number $260
and not the text "$260".

Rick

 

[Gary Brown]

=IF(K5<3,"$50",IF(AND(K5>2,K5<6),"$100",IF(AND(K5>5,K5<9),"$180",IF(AND(K5>8,K5<13),"$260",""))))

You keep wanting to do this...
K5>5<9
Excel can't handle that. It needs you to tell it what to compare each time...
K5>5 and K5<9

--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown

 

[Paula]

That worked, Dave. Thank you so much!

One more:

=IF(L42<3,50,IF(L42<5,100,""))

I don't see a reason to check for > 2 and < 5 in that second if().

And I changed the text to real numbers.

 

[Mike H]

Paula,

You would really do better posting the question you want the answer to in
the first place. here's the corrected syntax for this one

=IF(K5<3,"$50",IF(AND(K5>2,K5<6),"$100",IF(AND(K5>5,K5<9),"$180",IF(AND(K5>8,K5<13),"$260",""))))

Be aware you will hit a limitation within Excel if you try to nest if
statemenst 11 deep.

Mike

 

[RagDyer]

However, if you're looking to expand this to 11 possibilities,
you might try something like this:

=LOOKUP(K5,{0,3,6,9,12,15,18,21,24,27,30;50,100,180,260,340,420,500,580,660,740,820})

I guessed at your parameters.
You can correct them as needed.

 

[Harlan Grove]

You keep wanting to do this...
K5>5<9

Not wanting, doing. The OP keeps DOING this. What she WANTS is for
Excel to do what she means rather than use syntax Excel understands.

Excel can't handle that. . . .

...

Excel handles it just fine. Excel interprets it as

(K5>5)<9

but K5>5 is either TRUE or FALSE, so Excel treats the fill IF test as
either TRUE<9 or FALSE<9, but as far as Excel is concerned when
transition formula evaluation is DISABLED both TRUE and FALSE are
greater than any numeric values, so both TRUE<9 and FALSE<9 are
themselves BOTH ALWAYS FALSE.

...

First, is K5 a number or text that looks like a number? That is, what
does the formula =ISNUMBER(K5) return?

Next, your syntax is wrong. Your logic is also faulty. If the K5<3 in
the 1st IF call is FALSE, i.e., K5 >= 3, then it's necessarily true
that K5 > 2. What do you want to happen when K5 is EXACTLY EQUAL TO 2,
3, 5, 6, 8, 9, etc? I suspect your formula would work if you rewrote
it as either

=TEXT(IF(--K5<=2,50,IF(--K5<=5,100,IF(--K5<=8,180,
IF(--K5<=12,260,"")))),"$0")

or

=TEXT(IF(--K5<3,50,IF(--K5<6,100,IF(--K5<9,180,
IF(--K5<13,260,"")))),"$0")

The -- before the K5 references ensures K5 will be treated as a
numeric value rather than text.

 

[T. Valko]

Harlan, off topic...

I took a look at your bowling.xls file a few days ago.

There is a typo in the formula for the 10th frame.

The last IF reads:

....is(ISNUMBER(....

--
Biff
Microsoft Excel MVP

You keep wanting to do this...
K5>5<9

Not wanting, doing. The OP keeps DOING this. What she WANTS is for
Excel to do what she means rather than use syntax Excel understands.

Excel can't handle that. . . .

....

Excel handles it just fine. Excel interprets it as

(K5>5)<9

but K5>5 is either TRUE or FALSE, so Excel treats the fill IF test as
either TRUE<9 or FALSE<9, but as far as Excel is concerned when
transition formula evaluation is DISABLED both TRUE and FALSE are
greater than any numeric values, so both TRUE<9 and FALSE<9 are
themselves BOTH ALWAYS FALSE.

....

First, is K5 a number or text that looks like a number? That is, what
does the formula =ISNUMBER(K5) return?

Next, your syntax is wrong. Your logic is also faulty. If the K5<3 in
the 1st IF call is FALSE, i.e., K5 >= 3, then it's necessarily true
that K5 > 2. What do you want to happen when K5 is EXACTLY EQUAL TO 2,
3, 5, 6, 8, 9, etc? I suspect your formula would work if you rewrote
it as either

=TEXT(IF(--K5<=2,50,IF(--K5<=5,100,IF(--K5<=8,180,
IF(--K5<=12,260,"")))),"$0")

or

=TEXT(IF(--K5<3,50,IF(--K5<6,100,IF(--K5<9,180,
IF(--K5<13,260,"")))),"$0")

The -- before the K5 references ensures K5 will be treated as a
numeric value rather than text.