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. -- 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
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,FAL SE) 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 |
All times are GMT +1. The time now is 11:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com