Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup vs. if, help needed
hi;
I have this sheet that I am making where there are 5 test results, each with 3 possible inputs. I am using optionbuttons to enter the results. So I end up with a table that looks something like this: true false false true false false true false false true false false true false false Where true values could swap with either of the false values in each row. Now for my application I need to read these results to get an output, so there are quite a few possible combinations, like 250 if I calculated right! There are cases though that will give the same results so like, if assuming we start at a1, I could have only one result if a1=true and b2=true, regardless of the other three rows values, so this reduces the number of outcomes quite a lot. I have used if statements for most of the basic results but the whole 7 bracket limit issue is a bit of a problem for more specific cases. I am familiar with vlookup and referencing a lookup table, but checking 15 true/false values at once seems like a long way around. eg. vlookup(a1&a2&a3&b1...e3,reference table,2,0) then in the reference column 1 will have all possible results eg. truetruefalsetrue...(up to 15th true/false entry) and column 2 will have the text to display following each outcome. This is definitely possible but is this the best way to go about it or is there an easier method?? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup vs. if, help needed
Seems more that you have (2^3)=8 possible combinations for each row * 5 rows,
or 8^5 combinations = 32,768 combinations. So..how are you trying to summarize/use this data? "CraigSA" wrote: hi; I have this sheet that I am making where there are 5 test results, each with 3 possible inputs. I am using optionbuttons to enter the results. So I end up with a table that looks something like this: true false false true false false true false false true false false true false false Where true values could swap with either of the false values in each row. Now for my application I need to read these results to get an output, so there are quite a few possible combinations, like 250 if I calculated right! There are cases though that will give the same results so like, if assuming we start at a1, I could have only one result if a1=true and b2=true, regardless of the other three rows values, so this reduces the number of outcomes quite a lot. I have used if statements for most of the basic results but the whole 7 bracket limit issue is a bit of a problem for more specific cases. I am familiar with vlookup and referencing a lookup table, but checking 15 true/false values at once seems like a long way around. eg. vlookup(a1&a2&a3&b1...e3,reference table,2,0) then in the reference column 1 will have all possible results eg. truetruefalsetrue...(up to 15th true/false entry) and column 2 will have the text to display following each outcome. This is definitely possible but is this the best way to go about it or is there an easier method?? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup vs. if, help needed
CraigSA
Questions: 1)Do you want the combination of Col_A and Col_B to count as one value, resulting in only 4 options (T/T, T/F, F/T, FF)? 2)Do you want to match the entire matrix and find a corresponding value for that configuration? 3)What values do you want associated with the TRUE/FALSE combinations? *********** Regards, Ron XL2002, WinXP "CraigSA" wrote: hi; I have this sheet that I am making where there are 5 test results, each with 3 possible inputs. I am using optionbuttons to enter the results. So I end up with a table that looks something like this: true false false true false false true false false true false false true false false Where true values could swap with either of the false values in each row. Now for my application I need to read these results to get an output, so there are quite a few possible combinations, like 250 if I calculated right! There are cases though that will give the same results so like, if assuming we start at a1, I could have only one result if a1=true and b2=true, regardless of the other three rows values, so this reduces the number of outcomes quite a lot. I have used if statements for most of the basic results but the whole 7 bracket limit issue is a bit of a problem for more specific cases. I am familiar with vlookup and referencing a lookup table, but checking 15 true/false values at once seems like a long way around. eg. vlookup(a1&a2&a3&b1...e3,reference table,2,0) then in the reference column 1 will have all possible results eg. truetruefalsetrue...(up to 15th true/false entry) and column 2 will have the text to display following each outcome. This is definitely possible but is this the best way to go about it or is there an easier method?? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup vs. if, help needed
No there are only 3 combinations for each row (TFF, FTF, FFT) only one of the
three can be true because each row is linked to a group of option buttons. so thats 3^5 right? Anyway, I'm using the data to give results of Hepatitis B virus testing. so colunm a is "Positive", b is "Negative", c is "No result". and there are 5 different tests. Now for each combination of test results there is a different diagnosis. Say if all the tests are negative then result is "Non-infectious". but if a1 is positive then there could be a number of different outcomes depending on the results of other tests. I was thinking of using vlookup with a reference table so that the outcome for one set would look like: eg. TFF,FTF,TFF,TFF,FFT ; and then my result would be something like: Chronic precore infection ; I was just wondering if there would be a better way of getting results because doing things this way will force me to make a lookup table thats 100+ rows long and this could get confusing, with each entry consisting of a combination of 15 T/F states and it would take time to set this up. I can use a few if statements in between for don't care combinations like: if(and(a1,b1),"my result","vlookup(....)") because if these 2 are positive it doesn't matter what the other three test results are. Is ther a way to ake the vlookup input an array istead of using &? so i would have vlookup(a1:c5,lookup table,2,0) instead of vlookup(a1&b1&c1&a2&b2&c2&a3&b3&c3&a4&b4&c4&a5&b5& c5, ..... ) When i try this I get #value. Or is there another formula i can use to make the whole process easier? Hope that clears things up a bit. "Duke Carey" wrote: Seems more that you have (2^3)=8 possible combinations for each row * 5 rows, or 8^5 combinations = 32,768 combinations. So..how are you trying to summarize/use this data? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup vs. if, help needed
I'm using an old version 2000.
other info is in my reply to Duke. thanx "Ron Coderre" wrote: CraigSA Questions: 1)Do you want the combination of Col_A and Col_B to count as one value, resulting in only 4 options (T/T, T/F, F/T, FF)? 2)Do you want to match the entire matrix and find a corresponding value for that configuration? 3)What values do you want associated with the TRUE/FALSE combinations? *********** Regards, Ron XL2002, WinXP |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup vs. if, help needed
CraigSA
See if this gets you headed in the right direction: Since it seems that you are only interested in the location of T's in the 3X5 matrix, use this technique to convert the postions to a numeric text string: For T's and F's in A1:C5 D1: =MOD(SEARCH("T",A1&B1&C1&"T"),4)&MOD(SEARCH("T",A2 &B2&C2&"T"),4)&MOD(SEARCH("T",A3&B3&C3&"T"),4)&MOD (SEARCH("T",A4&B4&C4&"T"),4)&MOD(SEARCH("T",A5&B5& C5&"T"),4) Using that fomrula this configuration: FFF FFF FFT TFF FTF Becomes: 00312 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "CraigSA" wrote: I'm using an old version 2000. other info is in my reply to Duke. thanx "Ron Coderre" wrote: CraigSA Questions: 1)Do you want the combination of Col_A and Col_B to count as one value, resulting in only 4 options (T/T, T/F, F/T, FF)? 2)Do you want to match the entire matrix and find a corresponding value for that configuration? 3)What values do you want associated with the TRUE/FALSE combinations? *********** Regards, Ron XL2002, WinXP |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup vs. if, help needed
Assuming you can work with the 5-number code my previous post suggested,
here's a more concise formula for generating it: D1: =RIGHT(100000+SUMPRODUCT((A1:C5="T")*COLUMN(A1:C5) *(10^(5-ROW(A1:C5)))),5) Or...if a basic numeric value would work for you: D1: =SUMPRODUCT((A1:C5="T")*COLUMN(A1:C5)*(10^(5-ROW(A1:C5)))) Either of those approaches could be used as the lookup value in a table. I hope that helps? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: CraigSA See if this gets you headed in the right direction: Since it seems that you are only interested in the location of T's in the 3X5 matrix, use this technique to convert the postions to a numeric text string: For T's and F's in A1:C5 D1: =MOD(SEARCH("T",A1&B1&C1&"T"),4)&MOD(SEARCH("T",A2 &B2&C2&"T"),4)&MOD(SEARCH("T",A3&B3&C3&"T"),4)&MOD (SEARCH("T",A4&B4&C4&"T"),4)&MOD(SEARCH("T",A5&B5& C5&"T"),4) Using that fomrula this configuration: FFF FFF FFT TFF FTF Becomes: 00312 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "CraigSA" wrote: I'm using an old version 2000. other info is in my reply to Duke. thanx "Ron Coderre" wrote: CraigSA Questions: 1)Do you want the combination of Col_A and Col_B to count as one value, resulting in only 4 options (T/T, T/F, F/T, FF)? 2)Do you want to match the entire matrix and find a corresponding value for that configuration? 3)What values do you want associated with the TRUE/FALSE combinations? *********** Regards, Ron XL2002, WinXP |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup vs. if, help needed
Hi Ron,
Your suggestion looks good. I ran out of time though and ended up creating my table with the t/f's. eg. tff,fft,ftf,fft,tff (for like 100 entries) It got a bit confusing at times as I'm sure you can imagine. I think I'll change it though to the numeric format as this would be a lot easier to follow and type out. Thanx for the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP help needed PLEASE... | Excel Worksheet Functions | |||
Help needed on VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup Help Needed | Excel Discussion (Misc queries) | |||
Vlookup Alternative Needed | Excel Discussion (Misc queries) | |||
VLOOKUP help needed! | Excel Worksheet Functions |