Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Outlining in nested subtotals not working | Excel Worksheet Functions | |||
Countif with nested function not working? | Excel Worksheet Functions | |||
COMPARING DATES with nested IF not working- Syntax is correct though | Excel Discussion (Misc queries) | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Nested IF(AND is not working | Excel Worksheet Functions |