nestIf function (6 functions)

L

Lee

I have raw data at Columns "A-F", can anyone offer a help
to write a nestIf function to get a resutl displayed at
Columns "I-L".


ColA,ColB,Col-C,ColD,ColE,Col-F,,,ColI,ColJ,ColK,col-L
1,0,0,1,0,0,,,1,0,0,0
1,0,0,0,1,0,,,0,1,0,0
1,0,0,0,0,1,,,0,0,0,1
0,1,0,1,0,0,,,0,1,0,0
0,1,0,0,1,0,,,0,0,1,0
0,1,0,0,0,1,,,0,0,0,1
0,0,1,1,0,0,,,0,0,0,1
0,0,1,0,1,0,,,0,0,0,1
0,0,1,0,0,1,,,0,0,0,1


According to Chip Pearson's web site, you cannot "nest"
more than 7 functions. If you count these raw data in a
special way, you only need to "nest" 6 functions.

For example,
1). Anytime, if Col-C is "1", then col-L will be "1".
2). anytime, if Col-F is "1", then col-L will be "1".
3). These will leave out only other 4 possible situations.

Thanks in advance,
 
L

Lee

I made a little progress so far. It works on column Col-L.

=IF((C8=1)+(F8=1),1,0)

This will take care both 1) and 2) conditions.
Any advice to take care the other 4 conditions?
 
R

Ron Rosenfeld

I have raw data at Columns "A-F", can anyone offer a help
to write a nestIf function to get a resutl displayed at
Columns "I-L".


ColA,ColB,Col-C,ColD,ColE,Col-F,,,ColI,ColJ,ColK,col-L
1,0,0,1,0,0,,,1,0,0,0
1,0,0,0,1,0,,,0,1,0,0
1,0,0,0,0,1,,,0,0,0,1
0,1,0,1,0,0,,,0,1,0,0
0,1,0,0,1,0,,,0,0,1,0
0,1,0,0,0,1,,,0,0,0,1
0,0,1,1,0,0,,,0,0,0,1
0,0,1,0,1,0,,,0,0,0,1
0,0,1,0,0,1,,,0,0,0,1


According to Chip Pearson's web site, you cannot "nest"
more than 7 functions. If you count these raw data in a
special way, you only need to "nest" 6 functions.

For example,
1). Anytime, if Col-C is "1", then col-L will be "1".
2). anytime, if Col-F is "1", then col-L will be "1".
3). These will leave out only other 4 possible situations.

Thanks in advance,
Click to expand...

It is not clear what the "other 4 possible situations" are. There are 64
possible combinations if columns A-F can be either a 1 or a 0.




--ron
 
L

Lee

I am glad, I became a little smart today.
This issue was just completely resolved by myself.
 
L

Lee

Ron,

I would like to drop a note with "Thank you" message for
looking into this issue. You are correct, there are 2^6
combinations if you consider them as isolate events.

However, in my applications, there are only 9 possible
combinations. All the original states and possible
outcomes were listed at this post.

Thanks,
 
R

Ron Rosenfeld

Ron,

I would like to drop a note with "Thank you" message for
looking into this issue. You are correct, there are 2^6
combinations if you consider them as isolate events.

However, in my applications, there are only 9 possible
combinations. All the original states and possible
outcomes were listed at this post.

Thanks,
Click to expand...

OK Here's one solution:

I: =IF(CONCATENATE(A1,B1,C1,D1,E1,F1)="100100",1,0)

J: =IF(OR(CONCATENATE(A1,B1,C1,D1,E1,F1)="100010",
CONCATENATE(A1,B1,C1,D1,E1,F1)="010100"),1,0)

K: =IF(CONCATENATE(A1,B1,C1,D1,E1,F1)="010010",1,0)

L: =IF(OR(CONCATENATE(A1,B1,C1,D1,E1,F1)="100001",
BIN2DEC(CONCATENATE(A1,B1,C1,D1,E1,F1))<=17),1,0)


--ron
 
Top