Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If, VLookup, Index, or a combination of all of them?
I've gone through many of the posts which answer queries on the above
functions, but haven't found one that I can apply to what I'm trying to do. There are four columns in particular that I want to analyse in a multi-column spreadsheet. THe four columns contain several different numerical codes. I want to return a specific value (which I create myself) in another column depending on how many of the four columns contain a particular code and which columns they happen to be. For example if I'm searching for the Code 1 and these are four of the rows... Term 1-----Term 2-----Term 3-----Term 4 1 ----- 1 ----- 4 ----- 10 0 ----- 1 ----- 1 ----- 1 1 ----- 2 ----- 1 ----- 0 4 ----- 0 ----- 0 ----- 1 .... what I would like to appear in the column where the formula will be for each of these four rows is: 07.12 (because the code 1 appears in Terms 1 and 2) 07.234 (because the code 1 appears in Terms 2, 3 and 4 07.13 and so on.) 07.4 I'm using Excel 2003 and while the following formula would do what I'd like it to in Excel 2007 (at least I think it would... ), I can't use more than 7 nested arguments: =if(AND(AK2=1,AL2=1,AM2=1,AN2=1),07.1234,if(and(AK 2=1,AL2=1,AM2=1,AN2=0),07.123,if(and(AK2=1,AL2=1,A M2=0,AN2=0),07.12,if(and(AK2=1,AL2=0,AM2=0,AN2=0), 07.1,if(and(AK2=0,AL2=1,AM2=1,AN2=1),07.234,if(and (AK2=0,AL2=0,AM2=1,AN2=1),07.34,if(and(AK2=0,AL2=0 ,AM2=0,AN2=1),07.4,if(and(AK2=0,AL2=1,AM2=1,AN2=0) ,07.23,if(and(AK2=0,AL2=1,AM2=0,AN2=0),07.2,if(and (AK2=0,AL2=0,AM2=1,AN2=0),07.3,if(and(AK2=1,AL2=1, AM2=0,AN2=1),07.124,if(and(AK2=1,AL2=0,AM2=1,AN2=1 ),07.134,if(and(AK2=1,AL2=0,AM2=0,AN2=1),07.14,if( and(AK2=1,AL2=0,AM2=1,AN2=0),07.13,if(and(AK2=0,AL 2=1,AM2=0,AN2=1),07.24,0))))))))))))))) Any help would be greatly appreciated! Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If, VLookup, Index, or a combination of all of them?
What result do you want if no cells contain 1?
Try something like this: ="07."&IF(A2=1,1,"")&IF(B2=1,2,"")&IF(C2=1,3,"")&I F(D2=1,4,"") -- Biff Microsoft Excel MVP "LisaM" wrote in message ... I've gone through many of the posts which answer queries on the above functions, but haven't found one that I can apply to what I'm trying to do. There are four columns in particular that I want to analyse in a multi-column spreadsheet. THe four columns contain several different numerical codes. I want to return a specific value (which I create myself) in another column depending on how many of the four columns contain a particular code and which columns they happen to be. For example if I'm searching for the Code 1 and these are four of the rows... Term 1-----Term 2-----Term 3-----Term 4 1 ----- 1 ----- 4 ----- 10 0 ----- 1 ----- 1 ----- 1 1 ----- 2 ----- 1 ----- 0 4 ----- 0 ----- 0 ----- 1 ... what I would like to appear in the column where the formula will be for each of these four rows is: 07.12 (because the code 1 appears in Terms 1 and 2) 07.234 (because the code 1 appears in Terms 2, 3 and 4 07.13 and so on.) 07.4 I'm using Excel 2003 and while the following formula would do what I'd like it to in Excel 2007 (at least I think it would... ), I can't use more than 7 nested arguments: =if(AND(AK2=1,AL2=1,AM2=1,AN2=1),07.1234,if(and(AK 2=1,AL2=1,AM2=1,AN2=0),07.123,if(and(AK2=1,AL2=1,A M2=0,AN2=0),07.12,if(and(AK2=1,AL2=0,AM2=0,AN2=0), 07.1,if(and(AK2=0,AL2=1,AM2=1,AN2=1),07.234,if(and (AK2=0,AL2=0,AM2=1,AN2=1),07.34,if(and(AK2=0,AL2=0 ,AM2=0,AN2=1),07.4,if(and(AK2=0,AL2=1,AM2=1,AN2=0) ,07.23,if(and(AK2=0,AL2=1,AM2=0,AN2=0),07.2,if(and (AK2=0,AL2=0,AM2=1,AN2=0),07.3,if(and(AK2=1,AL2=1, AM2=0,AN2=1),07.124,if(and(AK2=1,AL2=0,AM2=1,AN2=1 ),07.134,if(and(AK2=1,AL2=0,AM2=0,AN2=1),07.14,if( and(AK2=1,AL2=0,AM2=1,AN2=0),07.13,if(and(AK2=0,AL 2=1,AM2=0,AN2=1),07.24,0))))))))))))))) Any help would be greatly appreciated! Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If, VLookup, Index, or a combination of all of them?
="07."&IF(A2=1,1,"")&IF(B2=1,2,"")&IF(C2=1,3,"")&I F(D2=1,4,"")
adjust cell references to suit "LisaM" wrote: I've gone through many of the posts which answer queries on the above functions, but haven't found one that I can apply to what I'm trying to do. There are four columns in particular that I want to analyse in a multi-column spreadsheet. THe four columns contain several different numerical codes. I want to return a specific value (which I create myself) in another column depending on how many of the four columns contain a particular code and which columns they happen to be. For example if I'm searching for the Code 1 and these are four of the rows... Term 1-----Term 2-----Term 3-----Term 4 1 ----- 1 ----- 4 ----- 10 0 ----- 1 ----- 1 ----- 1 1 ----- 2 ----- 1 ----- 0 4 ----- 0 ----- 0 ----- 1 ... what I would like to appear in the column where the formula will be for each of these four rows is: 07.12 (because the code 1 appears in Terms 1 and 2) 07.234 (because the code 1 appears in Terms 2, 3 and 4 07.13 and so on.) 07.4 I'm using Excel 2003 and while the following formula would do what I'd like it to in Excel 2007 (at least I think it would... ), I can't use more than 7 nested arguments: =if(AND(AK2=1,AL2=1,AM2=1,AN2=1),07.1234,if(and(AK 2=1,AL2=1,AM2=1,AN2=0),07.123,if(and(AK2=1,AL2=1,A M2=0,AN2=0),07.12,if(and(AK2=1,AL2=0,AM2=0,AN2=0), 07.1,if(and(AK2=0,AL2=1,AM2=1,AN2=1),07.234,if(and (AK2=0,AL2=0,AM2=1,AN2=1),07.34,if(and(AK2=0,AL2=0 ,AM2=0,AN2=1),07.4,if(and(AK2=0,AL2=1,AM2=1,AN2=0) ,07.23,if(and(AK2=0,AL2=1,AM2=0,AN2=0),07.2,if(and (AK2=0,AL2=0,AM2=1,AN2=0),07.3,if(and(AK2=1,AL2=1, AM2=0,AN2=1),07.124,if(and(AK2=1,AL2=0,AM2=1,AN2=1 ),07.134,if(and(AK2=1,AL2=0,AM2=0,AN2=1),07.14,if( and(AK2=1,AL2=0,AM2=1,AN2=0),07.13,if(and(AK2=0,AL 2=1,AM2=0,AN2=1),07.24,0))))))))))))))) Any help would be greatly appreciated! Thank you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If, VLookup, Index, or a combination of all of them?
"T. Valko" wrote:
What result do you want if no cells contain 1? If there is no 1, for example, in a row, I'd like the returned value to be 0 (zero). Try something like this: ="07."&IF(A2=1,1,"")&IF(B2=1,2,"")&IF(C2=1,3,"")&I F(D2=1,4,"") -- Biff Microsoft Excel MVP This works perfect! And it was so simple!! Thank you very much for your help. :) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If, VLookup, Index, or a combination of all of them?
"Teethless mama" wrote:
="07."&IF(A2=1,1,"")&IF(B2=1,2,"")&IF(C2=1,3,"")&I F(D2=1,4,"") adjust cell references to suit Thank you very much for your help! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If, VLookup, Index, or a combination of all of them?
If there is no 1, for example, in a row,
I'd like the returned value to be 0 Do you mean just plain old 0 or 07.0 ? For plain old 0: =IF(COUNTIF(A2:D2,1)=0,0,"07."&IF(A2=1,1,"")&IF(B2 =1,2,"")&IF(C2=1,3,"")&IF(D2=1,4,"")) For 07.0: ="07."&IF(COUNTIF(A2:D2,1)=0,0,"")&IF(A2=1,1,"")&I F(B2=1,2,"")&IF(C2=1,3,"")&IF(D2=1,4,"") -- Biff Microsoft Excel MVP "LisaM" wrote in message ... "T. Valko" wrote: What result do you want if no cells contain 1? If there is no 1, for example, in a row, I'd like the returned value to be 0 (zero). Try something like this: ="07."&IF(A2=1,1,"")&IF(B2=1,2,"")&IF(C2=1,3,"")&I F(D2=1,4,"") -- Biff Microsoft Excel MVP This works perfect! And it was so simple!! Thank you very much for your help. :) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If, VLookup, Index, or a combination of all of them?
These are great! It was 'plain old 0' I was looking for, but I'm learning a
bit more about how to construct formulae (and the logic behind the construction) from your examples. Thank you for all your help. :) Lisa "T. Valko" wrote: For plain old 0: =IF(COUNTIF(A2:D2,1)=0,0,"07."&IF(A2=1,1,"")&IF(B2 =1,2,"")&IF(C2=1,3,"")&IF(D2=1,4,"")) For 07.0: ="07."&IF(COUNTIF(A2:D2,1)=0,0,"")&IF(A2=1,1,"")&I F(B2=1,2,"")&IF(C2=1,3,"")&IF(D2=1,4,"") -- Biff Microsoft Excel MVP |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If, VLookup, Index, or a combination of all of them?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "LisaM" wrote in message ... These are great! It was 'plain old 0' I was looking for, but I'm learning a bit more about how to construct formulae (and the logic behind the construction) from your examples. Thank you for all your help. :) Lisa "T. Valko" wrote: For plain old 0: =IF(COUNTIF(A2:D2,1)=0,0,"07."&IF(A2=1,1,"")&IF(B2 =1,2,"")&IF(C2=1,3,"")&IF(D2=1,4,"")) For 07.0: ="07."&IF(COUNTIF(A2:D2,1)=0,0,"")&IF(A2=1,1,"")&I F(B2=1,2,"")&IF(C2=1,3,"")&IF(D2=1,4,"") -- Biff Microsoft Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combination INDEX//MATCH and IF-functions | Excel Discussion (Misc queries) | |||
Index/Matching in combination w/Right() | Excel Worksheet Functions | |||
Index match combination | Excel Worksheet Functions | |||
Index / match combination | Excel Discussion (Misc queries) | |||
Combination...SumProduct, Index, Match? | Excel Worksheet Functions |