Logic Tree, MS help = useless

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.

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

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.

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

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.

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

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.

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

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.

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

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.

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

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.

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

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.

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

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.

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

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.

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

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.

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.

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

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.

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

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.

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

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.

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

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.

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

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.

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.

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

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.

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

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.

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>

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.

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

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.

On Mon, 22 Aug 2005 13:42:17 -0500, roffler
<roffler.1u6jfv_1124738142.5375@excelforum-nospam.com> 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)
>
>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

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?
--
Regards,

Dave


"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)
>
> 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.
>
>
> --
> roffler
> ------------------------------------------------------------------------
> roffler's Profile: http://www.excelforum.com/member.php...o&userid=26518
> View this thread: http://www.excelforum.com/showthread...hreadid=397875
>
>