Logic Tree, MS help = useless

[roffler]

My setup is this:
4 columns, with "yes" or "no" in each column, describing a set of
objects. I have a logic tree that my boss gave me, with 15 possible
integer outputs, corresponding to the yes/no paths each object might
take. (For example, a yes-> yes->no-> no path might give the number
10)

He wants me to automate the logic tree traversal, so if one of the
yes/no values is changed, the output value changes automatically, w/o
having to look up the logic tree... is there any way i can program in
the 15 different conditions (ie if A2=yes, and A3=yes, and A4 = no, and
A5= yes, output the number 3)? I tried using the IF/AND/OR operators,
but excel doesnt let you nest that many.

Any help that anyone gives would be more than appreciated, i'm
relatively inexperienced with Excel.

 

[Guest]

Is there any kind of logic to how the yes/no combinations map to the values?
For example, does a 'yes' in the first column mean add 3 and a 'no' in the
first column means add 5? Anything like that?

 

[Harlan Grove]

roffler wrote...

My setup is this:
4 columns, with "yes" or "no" in each column, describing a set of
objects. I have a logic tree that my boss gave me, with 15 possible
integer outputs, corresponding to the yes/no paths each object might
take. (For example, a yes-> yes->no-> no path might give the number
10)

....

With 4 cells containing Y/N, there are 16 (2^4), not 15, possible
combinations.

If your entry cells were B2:E2, you could use something as simple as

=LOOKUP(SUMPRODUCT(--(B2:E2="Yes"),2^{3,2,1,0}),ROW(INDIRECT("1:16"))-1,
<YourBoss'sMappingHere>)

SUMPRODUCT call returns values as follows.

B C D E SUMPRODUCT
N N N N 0
N N N Y 1
N N Y N 2
N N Y Y 3
N Y N N 4
N Y N Y 5
N Y Y N 6
N Y Y Y 7
Y N N N 8
Y N N Y 9
Y N Y N 10
Y N Y Y 11
Y Y N N 12
Y Y N Y 13
Y Y Y N 14
Y Y Y Y 15

Map these to your boss's desired results.

 

[Ron Rosenfeld]

My setup is this:
4 columns, with "yes" or "no" in each column, describing a set of
objects. I have a logic tree that my boss gave me, with 15 possible
integer outputs, corresponding to the yes/no paths each object might
take. (For example, a yes-> yes->no-> no path might give the number
10)

He wants me to automate the logic tree traversal, so if one of the
yes/no values is changed, the output value changes automatically, w/o
having to look up the logic tree... is there any way i can program in
the 15 different conditions (ie if A2=yes, and A3=yes, and A4 = no, and
A5= yes, output the number 3)? I tried using the IF/AND/OR operators,
but excel doesnt let you nest that many.

Any help that anyone gives would be more than appreciated, i'm
relatively inexperienced with Excel.

Well, I don't understand why there are only 15 possible outputs since there are
16 paths (2^4). Perhaps the output is zero-based and you are not counting the
zero? i.e. it is really 0 to 15?

Unless there is some logic to the relation between the integer output and the
yes/no paths, you'll have to use a lookup table.

If you have your four yes/no in A1:A4, and you setup a lookup table someplace
with all the combinations and associated integers, e.g E1:F16

yesyesyesyes 15
yesyesyesno 14
yesyesnoyes 13
yesyesnono 12
yesnoyesyes 11
yesnoyesno 10
yesnonoyes 9
yesnonono 8
noyesyesyes 7
noyesyesno 6
noyesnoyes 5
noyesnono 4
nonoyesyes 3
nonoyesno 2
nononoyes 1
nononono 0


And then use a lookup formula:

=VLOOKUP(CONCATENATE(A1,B1,C1,D1),$E$1:$F$16,2,FALSE)

If there is some logic to how the numbers are assigned, there might be other
solutions.

You will likely need to change my cell references depending on the setup of
your worksheet.



--ron