Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF not working.....
Here is the formula that I'm using:
=IF(K2={71876,147825,186110,73859,1183639,163773,1 40408,226473},"GSA",IF(K2={235842,245561,235823,23 5843,245337,239135,200830},"CNB",IF(K2={683983,245 144,29839,4813,67555,175821,82901,175645,139429,17 5397,131445,979352,113854,972262,174756,218880,335 8},"CB","ADJ"))) When K2 has any of the values listed in the first part of the IF it returns GSA, like it's supposed to. BUT....if any of the other values listed in the subsequent parts are in K2 it always list ADJ, like it's not in any of the other list. I can't get it to return CNB or CB. Any ideas why? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF not working.....
If I enter 235842 in K2, your formula returns CNB, and if I enter 683983, it
returns CB. I don't know for sure, but I might guess that Excel doesn't like more than one array of values in brackets in a single formula. It seems to accept the first value in each subsequent array and ignore the rest. Instead of the nested IFs, you could enter the numbers and the values they should return in a couple of empty columns, then use a VLOOKUP formula. If, for example, you entered your data like this in columns M & N: 71876 GSA 147825 GSA 186110 GSA 73859 GSA 1183639 GSA 16773 GSA 140408 GSA 226473 GSA 235842 CNB 245561 CNB 235823 CNB etc. Your Vlookup formula would be =IF(ISERROR(VLOOKUP(K2,M:N,2,FALSE)),"ADJ",VLOOKUP (K2,M:N,2,FALSE)) If it finds the K2 number in column M, the code next to it in column N is returned. If the K2 value is not found in column M, ADJ is returned. Hope this helps, Hutch "Patrick" wrote: Here is the formula that I'm using: =IF(K2={71876,147825,186110,73859,1183639,163773,1 40408,226473},"GSA",IF(K2={235842,245561,235823,23 5843,245337,239135,200830},"CNB",IF(K2={683983,245 144,29839,4813,67555,175821,82901,175645,139429,17 5397,131445,979352,113854,972262,174756,218880,335 8},"CB","ADJ"))) When K2 has any of the values listed in the first part of the IF it returns GSA, like it's supposed to. BUT....if any of the other values listed in the subsequent parts are in K2 it always list ADJ, like it's not in any of the other list. I can't get it to return CNB or CB. Any ideas why? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF not working.....
When K2 has any value in the first part of the formula other than 71876, it
returns "ADJ" for me. you'll need to use OR for your conditional tests. OR(K2={71876,147825,186110,73859,1183639,163773,14 0408,226473}) "Patrick" wrote: Here is the formula that I'm using: =IF(K2={71876,147825,186110,73859,1183639,163773,1 40408,226473},"GSA",IF(K2={235842,245561,235823,23 5843,245337,239135,200830},"CNB",IF(K2={683983,245 144,29839,4813,67555,175821,82901,175645,139429,17 5397,131445,979352,113854,972262,174756,218880,335 8},"CB","ADJ"))) When K2 has any of the values listed in the first part of the IF it returns GSA, like it's supposed to. BUT....if any of the other values listed in the subsequent parts are in K2 it always list ADJ, like it's not in any of the other list. I can't get it to return CNB or CB. Any ideas why? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF not working.....
That worked perfect. Thanks so much. I knew I was close.
"JMB" wrote: When K2 has any value in the first part of the formula other than 71876, it returns "ADJ" for me. you'll need to use OR for your conditional tests. OR(K2={71876,147825,186110,73859,1183639,163773,14 0408,226473}) "Patrick" wrote: Here is the formula that I'm using: =IF(K2={71876,147825,186110,73859,1183639,163773,1 40408,226473},"GSA",IF(K2={235842,245561,235823,23 5843,245337,239135,200830},"CNB",IF(K2={683983,245 144,29839,4813,67555,175821,82901,175645,139429,17 5397,131445,979352,113854,972262,174756,218880,335 8},"CB","ADJ"))) When K2 has any of the values listed in the first part of the IF it returns GSA, like it's supposed to. BUT....if any of the other values listed in the subsequent parts are in K2 it always list ADJ, like it's not in any of the other list. I can't get it to return CNB or CB. Any ideas why? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF not working.....
you're welcome.
"Patrick" wrote: That worked perfect. Thanks so much. I knew I was close. "JMB" wrote: When K2 has any value in the first part of the formula other than 71876, it returns "ADJ" for me. you'll need to use OR for your conditional tests. OR(K2={71876,147825,186110,73859,1183639,163773,14 0408,226473}) "Patrick" wrote: Here is the formula that I'm using: =IF(K2={71876,147825,186110,73859,1183639,163773,1 40408,226473},"GSA",IF(K2={235842,245561,235823,23 5843,245337,239135,200830},"CNB",IF(K2={683983,245 144,29839,4813,67555,175821,82901,175645,139429,17 5397,131445,979352,113854,972262,174756,218880,335 8},"CB","ADJ"))) When K2 has any of the values listed in the first part of the IF it returns GSA, like it's supposed to. BUT....if any of the other values listed in the subsequent parts are in K2 it always list ADJ, like it's not in any of the other list. I can't get it to return CNB or CB. Any ideas why? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested IF Function not working | Excel Worksheet Functions | |||
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) | |||
Nested IF(AND is not working | Excel Worksheet Functions |