Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Nesting more than nine IF statements in Excel
Excel 2000
I am trying to create a sheet which allows us to allocate sectors, and sub sectors to clients. So for Sony for example, their sector might be "Electronics" and their sub sector might be "Consumer Electronics". I have created a list from which people can select the sector, and was using IF statements within the list function within Data Valiudate to say: This is in the column to the right of the one with the sector list in =IF(AM2="FMT",FM,IF(AM2="T&T",TT,IF(AM2="Med",Me,I F(AM2="H&L",HL,IF (AM2="R&W",RW, IF(AM2="Hospitality and Leisure",HL,IF(AM2="BSHL",BS,IF (AM2="Man",M)))))))) Each returned value (FM, TT, Me...) refers to another list which then shows the sub sectors. So for example if AM2 = FMT (Financial Markets), AN2 provides a list with Banks, Insurance etc which is taken from another sheet. This works fine for the above formula but the problem is I need two more IFs to complete my sectors. Please help. -- Message posted via http://www.officekb.com |
#2
|
|||
|
|||
Hi
=CHOOSE(MATCH(AM2,{"FMT";"T&T";"Med";"H&L";"R&W";" Hospidality and Leisure";"BSHL";"Man";"xx1";"xx2"},0),TT,Me,HL,RW, HL,BS,M,yy1,yy2) Btw. HL is returned twice - is it meant so? -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Alexander Walsh via OfficeKB.com" wrote in message ... Excel 2000 I am trying to create a sheet which allows us to allocate sectors, and sub sectors to clients. So for Sony for example, their sector might be "Electronics" and their sub sector might be "Consumer Electronics". I have created a list from which people can select the sector, and was using IF statements within the list function within Data Valiudate to say: This is in the column to the right of the one with the sector list in =IF(AM2="FMT",FM,IF(AM2="T&T",TT,IF(AM2="Med",Me,I F(AM2="H&L",HL,IF (AM2="R&W",RW, IF(AM2="Hospitality and Leisure",HL,IF(AM2="BSHL",BS,IF (AM2="Man",M)))))))) Each returned value (FM, TT, Me...) refers to another list which then shows the sub sectors. So for example if AM2 = FMT (Financial Markets), AN2 provides a list with Banks, Insurance etc which is taken from another sheet. This works fine for the above formula but the problem is I need two more IFs to complete my sectors. Please help. -- Message posted via http://www.officekb.com |
#3
|
|||
|
|||
Thanks so much, I did not mean to have HL twice.
Basically you have two columns, one with Sector and one with Sub Sector. Colum AM has lists of 9 sectors, and I want to create a list in AN of Sub Sectors which depend on what was selected in AM. When I try the script you gave, Excel tells me I cannot use Arrays in the Validation thingy. -- Message posted via http://www.officekb.com |
#4
|
|||
|
|||
Sounds like this would be better done with a VLOOKUP() table. Put your
'codes' such as FMT and T&T in one column, then put the corresponding name in the adjacent column. Test your vlookup formula to make sure it works. Once you are satisfied it's returning the correct name, wrap it in an INDIRECT() - something like this: =INDIRECT(VLOOKUP(LookupCode,LookupTable,2,FALSE)) Duke "Alexander Walsh via OfficeKB.com" wrote in message ... Excel 2000 I am trying to create a sheet which allows us to allocate sectors, and sub sectors to clients. So for Sony for example, their sector might be "Electronics" and their sub sector might be "Consumer Electronics". I have created a list from which people can select the sector, and was using IF statements within the list function within Data Valiudate to say: This is in the column to the right of the one with the sector list in =IF(AM2="FMT",FM,IF(AM2="T&T",TT,IF(AM2="Med",Me,I F(AM2="H&L",HL,IF (AM2="R&W",RW, IF(AM2="Hospitality and Leisure",HL,IF(AM2="BSHL",BS,IF (AM2="Man",M)))))))) Each returned value (FM, TT, Me...) refers to another list which then shows the sub sectors. So for example if AM2 = FMT (Financial Markets), AN2 provides a list with Banks, Insurance etc which is taken from another sheet. This works fine for the above formula but the problem is I need two more IFs to complete my sectors. Please help. -- Message posted via http://www.officekb.com |
#5
|
|||
|
|||
You can end your exisitng formula with","Not yet""
(AM2="Man",M,"Not yet")))))))) Insert a helper column, and use this formula: =IF(<previous column<"Not yet",<previous column,IF(<add your new argument here,<add your result here,IF(<add your 2nd argument here,<add your 2nd result here,etc In this way, you retain the results from the previous column, unless it says Not yet. All those that have not yet been tested, are now tested. Use the new column as the source for the next formula, iso the one you are using now. "Alexander Walsh via OfficeKB.com" wrote: Excel 2000 I am trying to create a sheet which allows us to allocate sectors, and sub sectors to clients. So for Sony for example, their sector might be "Electronics" and their sub sector might be "Consumer Electronics". I have created a list from which people can select the sector, and was using IF statements within the list function within Data Valiudate to say: This is in the column to the right of the one with the sector list in =IF(AM2="FMT",FM,IF(AM2="T&T",TT,IF(AM2="Med",Me,I F(AM2="H&L",HL,IF (AM2="R&W",RW, IF(AM2="Hospitality and Leisure",HL,IF(AM2="BSHL",BS,IF (AM2="Man",M)))))))) Each returned value (FM, TT, Me...) refers to another list which then shows the sub sectors. So for example if AM2 = FMT (Financial Markets), AN2 provides a list with Banks, Insurance etc which is taken from another sheet. This works fine for the above formula but the problem is I need two more IFs to complete my sectors. Please help. -- Message posted via http://www.officekb.com |
#6
|
|||
|
|||
Thanks for this, though I am not sure I understand how to do that.
I have created a column with the "codes" in like FMT, T&T (by the way I would prefer to use Financial Markets and Technology and Telecoms if possible) and then made a column next to that with the name codes FM, TT etc; but I am not sure where to go from there with this vlookup thing. A -- Message posted via http://www.officekb.com |
#7
|
|||
|
|||
One thing I thought of was to make the names of my new list entries the
same as the value in my first list. So by selecting FMT in the first list, if I Name the second list FMT and tell it that I was the list source to be the Name of whatever the first list has got. I tried this by just entering the source as the first list, so in this case Source =AM2, but of course that means that it creates a list of just the value in that cell rather then knowing I want it to use that Named group of cells. Any ideas? -- Message posted via http://www.officekb.com |
#8
|
|||
|
|||
Thanks Kassie, but with your suggestion will I not need one IF statement
for each sector (FMT, T&T etc) either in the help column or within the Validate source area? -- Message posted via http://www.officekb.com |
#9
|
|||
|
|||
I think you simply need to set the source = INDIRECT(AM2)
"Alexander Walsh via OfficeKB.com" wrote in message ... One thing I thought of was to make the names of my new list entries the same as the value in my first list. So by selecting FMT in the first list, if I Name the second list FMT and tell it that I was the list source to be the Name of whatever the first list has got. I tried this by just entering the source as the first list, so in this case Source =AM2, but of course that means that it creates a list of just the value in that cell rather then knowing I want it to use that Named group of cells. Any ideas? -- Message posted via http://www.officekb.com |
#10
|
|||
|
|||
I have cracked it with the simplest solution ever!
I have the first column (Sector) as a normal list with a load of sectors in it. I then have a separate sheet with the sub-sectors in and each is named with the corresponding name identical to the Sector list above. In the Select Sub Sector column I then have a very simple thing in the list source area stating =INDIRECT(<Cell) and this means it takes the value in the previous one, which corresponds to a name, and pulls the list over. Job done.! -- Message posted via http://www.officekb.com |
#11
|
|||
|
|||
How would I apply this to my problem?
=If(O6<15,0,if(O6<16,.05,if(O6<17,.053.....,if(O6< 35,.14)))) Obviously there are more arguments than allowed. "Alexander Walsh via OfficeKB.com" wrote: I have cracked it with the simplest solution ever! I have the first column (Sector) as a normal list with a load of sectors in it. I then have a separate sheet with the sub-sectors in and each is named with the corresponding name identical to the Sector list above. In the Select Sub Sector column I then have a very simple thing in the list source area stating =INDIRECT(<Cell) and this means it takes the value in the previous one, which corresponds to a name, and pulls the list over. Job done.! -- Message posted via http://www.officekb.com |
#12
|
|||
|
|||
Hi
=CHOOSE(MATCH(O6,{0;15;16;.......34;35},1),0,0.5,0 .53,.......,0.14,"") (fill caps in formula) When there was a typo in your posting, and the last value for O6 was 45 instead of 35, then =AND(O6<46,O615)*(0.05+0.003*INT(O6-15)) Arvi Laanemets "Currie" wrote in message ... How would I apply this to my problem? =If(O6<15,0,if(O6<16,.05,if(O6<17,.053.....,if(O6< 35,.14)))) Obviously there are more arguments than allowed. "Alexander Walsh via OfficeKB.com" wrote: I have cracked it with the simplest solution ever! I have the first column (Sector) as a normal list with a load of sectors in it. I then have a separate sheet with the sub-sectors in and each is named with the corresponding name identical to the Sector list above. In the Select Sub Sector column I then have a very simple thing in the list source area stating =INDIRECT(<Cell) and this means it takes the value in the previous one, which corresponds to a name, and pulls the list over. Job done.! -- Message posted via http://www.officekb.com |
#13
|
|||
|
|||
=CHOOSE(MATCH(O6,{0;15;16;17;18;19;20;21;22;23;24; 25;26;27;28;29;30;31;32;33;34;35},1)*0,0.05,0.053, 0.055,0.058,0.062,0.065,0.069,0.072,0.076,0.081,0. 085,0.09,0.094,0.099,0.0105,0.11,0.116,0.121,0.127 ,0.134,0.14,"")
So there's my formula. I'm not sure what the * is about. I just got a dialog box that said my formula was incorrect and it made the correction for me. The value for O6 is 27.19 so the result of the formula should be .094. If that helps any in trying to figure it out then great! "Arvi Laanemets" wrote: Hi =CHOOSE(MATCH(O6,{0;15;16;.......34;35},1),0,0.5,0 .53,.......,0.14,"") (fill caps in formula) When there was a typo in your posting, and the last value for O6 was 45 instead of 35, then =AND(O6<46,O615)*(0.05+0.003*INT(O6-15)) Arvi Laanemets "Currie" wrote in message ... How would I apply this to my problem? =If(O6<15,0,if(O6<16,.05,if(O6<17,.053.....,if(O6< 35,.14)))) Obviously there are more arguments than allowed. "Alexander Walsh via OfficeKB.com" wrote: I have cracked it with the simplest solution ever! I have the first column (Sector) as a normal list with a load of sectors in it. I then have a separate sheet with the sub-sectors in and each is named with the corresponding name identical to the Sector list above. In the Select Sub Sector column I then have a very simple thing in the list source area stating =INDIRECT(<Cell) and this means it takes the value in the previous one, which corresponds to a name, and pulls the list over. Job done.! -- Message posted via http://www.officekb.com |
#14
|
|||
|
|||
Hi
Replace * with comma. Arvi Laanemets "Currie" wrote in message ... =CHOOSE(MATCH(O6,{0;15;16;17;18;19;20;21;22;23;24; 25;26;27;28;29;30;31;32;33 ;34;35},1)*0,0.05,0.053,0.055,0.058,0.062,0.065,0. 069,0.072,0.076,0.081,0.08 5,0.09,0.094,0.099,0.0105,0.11,0.116,0.121,0.127,0 .134,0.14,"") So there's my formula. I'm not sure what the * is about. I just got a dialog box that said my formula was incorrect and it made the correction for me. The value for O6 is 27.19 so the result of the formula should be ..094. If that helps any in trying to figure it out then great! "Arvi Laanemets" wrote: Hi =CHOOSE(MATCH(O6,{0;15;16;.......34;35},1),0,0.5,0 .53,.......,0.14,"") (fill caps in formula) When there was a typo in your posting, and the last value for O6 was 45 instead of 35, then =AND(O6<46,O615)*(0.05+0.003*INT(O6-15)) Arvi Laanemets "Currie" wrote in message ... How would I apply this to my problem? =If(O6<15,0,if(O6<16,.05,if(O6<17,.053.....,if(O6< 35,.14)))) Obviously there are more arguments than allowed. "Alexander Walsh via OfficeKB.com" wrote: I have cracked it with the simplest solution ever! I have the first column (Sector) as a normal list with a load of sectors in it. I then have a separate sheet with the sub-sectors in and each is named with the corresponding name identical to the Sector list above. In the Select Sub Sector column I then have a very simple thing in the list source area stating =INDIRECT(<Cell) and this means it takes the value in the previous one, which corresponds to a name, and pulls the list over. Job done.! -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
I get a program error when I download an excel template | Excel Discussion (Misc queries) | |||
html to excel | Excel Discussion (Misc queries) | |||
Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel | |||
Merge from Excel to Excel | Excel Discussion (Misc queries) |