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