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.