| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested Functions Limit
I was wondering if there is a way to re-write the formula below or nest more
than 7 IF scenarios. I need to be able to check for several scenarios or more than 7. Is this possible? =IF(ISNUMBER(SEARCH("TIER 1",$C3,1)),"TIER 1",IF(ISNUMBER(SEARCH("TIER 2",$C3,1)),"TIER 2",IF(ISNUMBER(SEARCH("TIER 3",$C3,1)),"TIER 3",IF(ISNUMBER(SEARCH("Chicago",$C3,1)),"Chicago", IF(ISNUMBER(SEARCH("St. Louis",$C3,1)),"St. Louis","Not Valid"))))) -- Thank you for your help and support |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested Functions Limit
It depends. You might be able to create a lookup table assuming that only
one search criteria will be present in the string. For example, you have separate criteria for "Tier 1" and "Chicago". As long as C3 doesn't contain both "Tier 1" and "Chicago" at the same time it should be doable. -- Biff Microsoft Excel MVP "bbal20" wrote in message ... I was wondering if there is a way to re-write the formula below or nest more than 7 IF scenarios. I need to be able to check for several scenarios or more than 7. Is this possible? =IF(ISNUMBER(SEARCH("TIER 1",$C3,1)),"TIER 1",IF(ISNUMBER(SEARCH("TIER 2",$C3,1)),"TIER 2",IF(ISNUMBER(SEARCH("TIER 3",$C3,1)),"TIER 3",IF(ISNUMBER(SEARCH("Chicago",$C3,1)),"Chicago", IF(ISNUMBER(SEARCH("St. Louis",$C3,1)),"St. Louis","Not Valid"))))) -- Thank you for your help and support |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested Functions Limit
hi
could you give an example of what would be in C3? As to number of nested ifs, in 2003, you are limited to 7. in 2007, this has been increased to 16(i think). Regards FSt1 "bbal20" wrote: I was wondering if there is a way to re-write the formula below or nest more than 7 IF scenarios. I need to be able to check for several scenarios or more than 7. Is this possible? =IF(ISNUMBER(SEARCH("TIER 1",$C3,1)),"TIER 1",IF(ISNUMBER(SEARCH("TIER 2",$C3,1)),"TIER 2",IF(ISNUMBER(SEARCH("TIER 3",$C3,1)),"TIER 3",IF(ISNUMBER(SEARCH("Chicago",$C3,1)),"Chicago", IF(ISNUMBER(SEARCH("St. Louis",$C3,1)),"St. Louis","Not Valid"))))) -- Thank you for your help and support |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested Functions Limit
Perhaps a neater way to achieve it which is also easier to maintain ?
Assuming your core list of strings to be searched is housed in F3:F7, viz.: Tier 1 Tier 2 Tier 3 Chicago St. Louis think you could try this in say, D3, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula (instead of just pressing ENTER): =IF(MAX(ISNUMBER(SEARCH(F3:F7,C3))*ROW(1:5))=0,"No t Valid",INDEX(F3:F7,MATCH(MAX(ISNUMBER(SEARCH(F3:F7 ,C3))*ROW(1:5)),ROW(1:5),0))) Adapt to suit: Change the "5" in the ROW(1:5) bits to equal the number of items/cells in your list of search-strings. Its 5 here as F3:F7 = 5 cells. If the search list is expanded to F3:F10 (8 cells), change the fig to 8, use ROW(1:8). Success? Celebrate it, click the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "bbal20" wrote: I was wondering if there is a way to re-write the formula below or nest more than 7 IF scenarios. I need to be able to check for several scenarios or more than 7. Is this possible? =IF(ISNUMBER(SEARCH("TIER 1",$C3,1)),"TIER 1",IF(ISNUMBER(SEARCH("TIER 2",$C3,1)),"TIER 2",IF(ISNUMBER(SEARCH("TIER 3",$C3,1)),"TIER 3",IF(ISNUMBER(SEARCH("Chicago",$C3,1)),"Chicago", IF(ISNUMBER(SEARCH("St. Louis",$C3,1)),"St. Louis","Not Valid"))))) -- Thank you for your help and support |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested Functions Limit
in 2007, this has been increased to 16(i think).
64 nested levels in Excel 2007. -- Biff Microsoft Excel MVP "FSt1" wrote in message ... hi could you give an example of what would be in C3? As to number of nested ifs, in 2003, you are limited to 7. in 2007, this has been increased to 16(i think). Regards FSt1 "bbal20" wrote: I was wondering if there is a way to re-write the formula below or nest more than 7 IF scenarios. I need to be able to check for several scenarios or more than 7. Is this possible? =IF(ISNUMBER(SEARCH("TIER 1",$C3,1)),"TIER 1",IF(ISNUMBER(SEARCH("TIER 2",$C3,1)),"TIER 2",IF(ISNUMBER(SEARCH("TIER 3",$C3,1)),"TIER 3",IF(ISNUMBER(SEARCH("Chicago",$C3,1)),"Chicago", IF(ISNUMBER(SEARCH("St. Louis",$C3,1)),"St. Louis","Not Valid"))))) -- Thank you for your help and support |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested Functions Limit
Hi,
A) The limit on nested function in 2007 is 64. B) You can use a formula of the following form in 2003 with no nested if problem: =IF(IF(ISNUMBER(SEARCH("TIER 1",$C3,1)),"TIER 1","")&IF(ISNUMBER(SEARCH("TIER 2",$C3,1)),"TIER 2","")&IF(ISNUMBER(SEARCH("TIER 3",$C3,1)),"TIER 3","")&IF(ISNUMBER(SEARCH("Chicago",$C3,1)),"Chica go","")&IF(ISNUMBER(SEARCH("St. Louis",$C3,1)),"St. Louis","")="","Not Valid",IF(ISNUMBER(SEARCH("TIER 1",$C3,1)),"TIER 1","")&IF(ISNUMBER(SEARCH("TIER 2",$C3,1)),"TIER 2","")&IF(ISNUMBER(SEARCH("TIER 3",$C3,1)),"TIER 3","")&IF(ISNUMBER(SEARCH("Chicago",$C3,1)),"Chica go","")&IF(ISNUMBER(SEARCH("St. Louis",$C3,1)),"St. Louis","")) C) In 2007 you can use a shorter approach: =IF(ISNUMBER(SEARCH("TIER 1",$C3,1)),"TIER 1","")&IF(ISNUMBER(SEARCH("TIER 2",$C3,1)),"TIER 2","")&IF(ISNUMBER(SEARCH("TIER 3",$C3,1)),"TIER 3","")&IF(ISNUMBER(SEARCH("Chicago",$C3,1)),"Chica go","")&IF(ISNUMBER(SEARCH("St. Louis",$C3,1)),"St. Louis","") To catch the Not Valid option apply a conditional formatting to the cell: 1. Choose Home, Condtional Formatting, New Rule, Use a formula to determine which cells to format, 2. Assuming this formula is in E6, enter the following formula =$E$6="" 3. Click Format, choose the Number tab, Custom and enter the following code into the Type line: ;;;"Not Valid" D) But the best choice would be a Lookup table in my opinion. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "bbal20" wrote: I was wondering if there is a way to re-write the formula below or nest more than 7 IF scenarios. I need to be able to check for several scenarios or more than 7. Is this possible? =IF(ISNUMBER(SEARCH("TIER 1",$C3,1)),"TIER 1",IF(ISNUMBER(SEARCH("TIER 2",$C3,1)),"TIER 2",IF(ISNUMBER(SEARCH("TIER 3",$C3,1)),"TIER 3",IF(ISNUMBER(SEARCH("Chicago",$C3,1)),"Chicago", IF(ISNUMBER(SEARCH("St. Louis",$C3,1)),"St. Louis","Not Valid"))))) -- Thank you for your help and support |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested Functions Limit
You can use a UDF function. put the search strings in a range of cells in
the workbook in the order you want them searched. Call with this worksheet function =SearchStings(A1:A10,$C3) Function SearchStings(SearchCriteria As Range, SearchString As String) SearchStings = "Not valid" For Each cell In SearchCriteria If InStr(SearchString, cell.Value) 0 Then SearchStings = cell.Value Exit For End If Next cell End Function "bbal20" wrote: I was wondering if there is a way to re-write the formula below or nest more than 7 IF scenarios. I need to be able to check for several scenarios or more than 7. Is this possible? =IF(ISNUMBER(SEARCH("TIER 1",$C3,1)),"TIER 1",IF(ISNUMBER(SEARCH("TIER 2",$C3,1)),"TIER 2",IF(ISNUMBER(SEARCH("TIER 3",$C3,1)),"TIER 3",IF(ISNUMBER(SEARCH("Chicago",$C3,1)),"Chicago", IF(ISNUMBER(SEARCH("St. Louis",$C3,1)),"St. Louis","Not Valid"))))) -- Thank you for your help and support |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested Functions Limit
Thanks. i knew it went up but was guessing on that one.
Regards FSt1 "T. Valko" wrote: in 2007, this has been increased to 16(i think). 64 nested levels in Excel 2007. -- Biff Microsoft Excel MVP "FSt1" wrote in message ... hi could you give an example of what would be in C3? As to number of nested ifs, in 2003, you are limited to 7. in 2007, this has been increased to 16(i think). Regards FSt1 "bbal20" wrote: I was wondering if there is a way to re-write the formula below or nest more than 7 IF scenarios. I need to be able to check for several scenarios or more than 7. Is this possible? =IF(ISNUMBER(SEARCH("TIER 1",$C3,1)),"TIER 1",IF(ISNUMBER(SEARCH("TIER 2",$C3,1)),"TIER 2",IF(ISNUMBER(SEARCH("TIER 3",$C3,1)),"TIER 3",IF(ISNUMBER(SEARCH("Chicago",$C3,1)),"Chicago", IF(ISNUMBER(SEARCH("St. Louis",$C3,1)),"St. Louis","Not Valid"))))) -- Thank you for your help and support |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested Functions Limit
Haven't yet seen a monster formula with that many nested functions!
-- Biff Microsoft Excel MVP "FSt1" wrote in message ... Thanks. i knew it went up but was guessing on that one. Regards FSt1 "T. Valko" wrote: in 2007, this has been increased to 16(i think). 64 nested levels in Excel 2007. -- Biff Microsoft Excel MVP "FSt1" wrote in message ... hi could you give an example of what would be in C3? As to number of nested ifs, in 2003, you are limited to 7. in 2007, this has been increased to 16(i think). Regards FSt1 "bbal20" wrote: I was wondering if there is a way to re-write the formula below or nest more than 7 IF scenarios. I need to be able to check for several scenarios or more than 7. Is this possible? =IF(ISNUMBER(SEARCH("TIER 1",$C3,1)),"TIER 1",IF(ISNUMBER(SEARCH("TIER 2",$C3,1)),"TIER 2",IF(ISNUMBER(SEARCH("TIER 3",$C3,1)),"TIER 3",IF(ISNUMBER(SEARCH("Chicago",$C3,1)),"Chicago", IF(ISNUMBER(SEARCH("St. Louis",$C3,1)),"St. Louis","Not Valid"))))) -- Thank you for your help and support |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need to get around limit of 7 nested functions | Excel Worksheet Functions | |||
| Limit to nested IF(ISNUMBER(SEARCH)) functions? | Excel Worksheet Functions | |||
| Nested If Limit | Excel Worksheet Functions | |||
| limit of 7 nested functions? | Excel Worksheet Functions | |||
| Nested IF limit or Open parentheses limit | Excel Discussion (Misc queries) | |||
Linear Mode
