Logic Tree, MS help = useless

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

roffler - 22 Aug 2005 19:42 GMT

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.

Signature

roffler

Reply to this Message

DaveB - 22 Aug 2005 21:17 GMT

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?

Signature

Regards,

Dave

> My setup is this:
> 4 columns, with "yes" or "no" in each column, describing a set of
[quoted text clipped - 12 lines]
> Any help that anyone gives would be more than appreciated, i'm
> relatively inexperienced with Excel.

Reply to this Message

Harlan Grove - 22 Aug 2005 21:35 GMT

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.

Reply to this Message

Ron Rosenfeld - 22 Aug 2005 21:42 GMT

>My setup is this:
>4 columns, with "yes" or "no" in each column, describing a set of
[quoted text clipped - 12 lines]
>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

Reply to this Message