Nested IF Function not working
 

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

Thank You.

Nested IF Function not working
 

The 2nd If( ) function has some issues.
I THINK you mean...
=if(L42<3,"$50",if(and(L422,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


"Paula" wrote:

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

Thank You.

Nested IF Function not working
 

Paula

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

Mike

"Paula" wrote:

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

Thank You.

Nested IF Function not working
 

One mo

=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 wrote:

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

Thank You.

--

Dave Peterson

Nested IF Function not working
 

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(K52<6),"$100",IF(AND(K55<9 ),"$180",IF(AND(K58<13),"$260",""))))

"Mike H" wrote:

Paula

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

Mike

"Paula" wrote:

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

Thank You.

Nested IF Function not working
 

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


"Paula" wrote in message
...
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(K52<6),"$100",IF(AND(K55<9 ),"$180",IF(AND(K58<13),"$260",""))))

"Mike H" wrote:

Paula

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

Mike

"Paula" wrote:

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

Thank You.

Nested IF Function not working
 

=IF(K5<3,"$50",IF(AND(K52,K5<6),"$100",IF(AND(K5 5,K5<9),"$180",IF(AND(K58,K5<13),"$260",""))))

You keep wanting to do this...
K55<9
Excel can't handle that. It needs you to tell it what to compare each time...
K55 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" wrote:

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(K52<6),"$100",IF(AND(K55<9 ),"$180",IF(AND(K58<13),"$260",""))))

"Mike H" wrote:

Paula

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

Mike

"Paula" wrote:

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

Thank You.

Nested IF Function not working
 

That worked, Dave. Thank you so much!

"Dave Peterson" wrote:

One mo

=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 wrote:

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

Thank You.

--

Dave Peterson

Nested IF Function not working
 

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(K52,K5<6),"$100",IF(AND(K5 5,K5<9),"$180",IF(AND(K58,K5<13),"$260",""))))

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

Mike

"Paula" wrote:

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(K52<6),"$100",IF(AND(K55<9 ),"$180",IF(AND(K58<13),"$260",""))))

"Mike H" wrote:

Paula

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

Mike

"Paula" wrote:

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

Thank You.

Nested IF Function not working
 

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,18 0,260,340,420,500,580,660,740,820})

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Paula" wrote in message
...
That worked, Dave. Thank you so much!

"Dave Peterson" wrote:

One mo

=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 wrote:

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

Thank You.

--

Dave Peterson

Nested IF Function not working
 

Gary Brown wrote...
...
You keep wanting to do this...
K55<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

(K55)<9

but K55 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.


"Paula" wrote:
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(K52<6),"$100",IF(AND(K55 <9),"$180",
IF(AND(K58<13),"$260",""))))
...

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.

Nested IF Function not working
 

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


"Harlan Grove" wrote in message
...
Gary Brown wrote...
....
You keep wanting to do this...
K55<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

(K55)<9

but K55 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.


"Paula" wrote:
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(K52<6),"$100",IF(AND(K55 <9),"$180",
IF(AND(K58<13),"$260",""))))
....

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.