Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this IF statement? need help.
This statement works fine but I want the cell to say 0" if the cell H6 has a
zero in it. As the stetments stands right now it displayes 6" in the cell if H6 is 0. How can I make that happen? =IF(H6<101,"6""",IF(AND(H6101.1,H6<151),"7""",IF( AND(H6151.1,H6<201),"8""",IF(AND(H6200.1,H6<275. 1),"9""",IF(AND(H6275.2,H6<350.1),"10""",IF(AND(H 6350.2,H6<601),"12""",IF(AND(H6601.1,H6<901),"14 """))))))) Please help me correct this statment. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this IF statement? need help.
You're approaching the limit of nested functions. But since you're working with
text, you could just concatenate your strings: =IF(H6=0,"0""","") &IF(AND(H60,H6<101),"6""","") &IF(AND(H6101.1,H6<151),"7""","") &IF(AND(H6151.1,H6<201),"8""","") &IF(AND(H6200.1,H6<275.1),"9""","") &IF(AND(H6275.2,H6<350.1),"10""","") &IF(AND(H6350.2,H6<601),"12""","") &IF(AND(H6601.1,H6<901),"14""","") But what happens at 101, 151, 201, ...? I would think you'd want <= or = in a few of those statements. AC man wrote: This statement works fine but I want the cell to say 0" if the cell H6 has a zero in it. As the stetments stands right now it displayes 6" in the cell if H6 is 0. How can I make that happen? =IF(H6<101,"6""",IF(AND(H6101.1,H6<151),"7""",IF( AND(H6151.1,H6<201),"8""",IF(AND(H6200.1,H6<275. 1),"9""",IF(AND(H6275.2,H6<350.1),"10""",IF(AND(H 6350.2,H6<601),"12""",IF(AND(H6601.1,H6<901),"14 """))))))) Please help me correct this statment. Thank you. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this IF statement? need help.
Not sure but I think you reached the max for that formula. Have a look at
this lookup formula instead. =LOOKUP(H6,{0,.1,101.1,151.1,201.1,275.2,350.2,601 .1,901.1},{0,6,7,8,9,10,12,14,14})&CHAR(34) 901.1 and above will result in 14" .....you will need to change it if that is not the correct result you want by changing the last "14" in the second bracket. HTH JG "AC man" wrote: This statement works fine but I want the cell to say 0" if the cell H6 has a zero in it. As the stetments stands right now it displayes 6" in the cell if H6 is 0. How can I make that happen? =IF(H6<101,"6""",IF(AND(H6101.1,H6<151),"7""",IF( AND(H6151.1,H6<201),"8""",IF(AND(H6200.1,H6<275. 1),"9""",IF(AND(H6275.2,H6<350.1),"10""",IF(AND(H 6350.2,H6<601),"12""",IF(AND(H6601.1,H6<901),"14 """))))))) Please help me correct this statment. Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this IF statement? need help.
I didn't notice the 101.1. But I'd still use:
&IF(AND(H6=101,H6<151),"7""","") for things like: 101.00001 Dave Peterson wrote: You're approaching the limit of nested functions. But since you're working with text, you could just concatenate your strings: =IF(H6=0,"0""","") &IF(AND(H60,H6<101),"6""","") &IF(AND(H6101.1,H6<151),"7""","") &IF(AND(H6151.1,H6<201),"8""","") &IF(AND(H6200.1,H6<275.1),"9""","") &IF(AND(H6275.2,H6<350.1),"10""","") &IF(AND(H6350.2,H6<601),"12""","") &IF(AND(H6601.1,H6<901),"14""","") But what happens at 101, 151, 201, ...? I would think you'd want <= or = in a few of those statements. AC man wrote: This statement works fine but I want the cell to say 0" if the cell H6 has a zero in it. As the stetments stands right now it displayes 6" in the cell if H6 is 0. How can I make that happen? =IF(H6<101,"6""",IF(AND(H6101.1,H6<151),"7""",IF( AND(H6151.1,H6<201),"8""",IF(AND(H6200.1,H6<275. 1),"9""",IF(AND(H6275.2,H6<350.1),"10""",IF(AND(H 6350.2,H6<601),"12""",IF(AND(H6601.1,H6<901),"14 """))))))) Please help me correct this statment. Thank you. -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
What is wrong with this IF statement? need help.
=IF(H6=0,0,IF(H6<101,"6""",IF(H6<151,"7""",IF(H6<2 01,"8""",IF(H6<275.1,"9""",IF(H6<350.1,"10""",IF(H 6<601,"12""",IF(H6<901,"14""",0))))))))
Try something like this out, adjust values, as needed ( in your example the figure 151 would not register as it you have less than 151 and in the next section you ask if its greater than 151 thus making this number void, you may have wanted to do this im not sure ? Pete -- (][ THIS EMAIL HAS BEEN SCANNED BY NORTON ANTIVIRUS ][) "AC man" wrote in message ... This statement works fine but I want the cell to say 0" if the cell H6 has a zero in it. As the stetments stands right now it displayes 6" in the cell if H6 is 0. How can I make that happen? =IF(H6<101,"6""",IF(AND(H6101.1,H6<151),"7""",IF( AND(H6151.1,H6<201),"8""",IF(AND(H6200.1,H6<275. 1),"9""",IF(AND(H6275.2,H6<350.1),"10""",IF(AND(H 6350.2,H6<601),"12""",IF(AND(H6601.1,H6<901),"14 """))))))) Please help me correct this statment. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Long IF Statement | Excel Discussion (Misc queries) | |||
If statement | Excel Discussion (Misc queries) | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
Statement | Excel Worksheet Functions | |||
Logic statement returns wrong answer. | Excel Worksheet Functions |