vlookup vs. if, help needed

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CraigSA - 11 May 2006 15:48 GMT

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

Reply to this Message

Duke Carey - 11 May 2006 16:01 GMT

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?

> hi;
>
[quoted text clipped - 26 lines]
> This is definitely possible but is this the best way to go about it or is
> there an easier method??

Reply to this Message

CraigSA - 12 May 2006 09:06 GMT

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.

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

Reply to this Message

Ron Coderre - 11 May 2006 16:01 GMT

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

> hi;
>
[quoted text clipped - 26 lines]
> This is definitely possible but is this the best way to go about it or is
> there an easier method??

Reply to this Message

CraigSA - 12 May 2006 09:30 GMT

I'm using an old version 2000.
other info is in my reply to Duke.
thanx

> CraigSA
>
[quoted text clipped - 12 lines]
>
> XL2002, WinXP

Reply to this Message

Ron Coderre - 12 May 2006 15:13 GMT

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

> I'm using an old version 2000.
> other info is in my reply to Duke.
[quoted text clipped - 16 lines]
> >
> > XL2002, WinXP

Reply to this Message

Ron Coderre - 12 May 2006 16:02 GMT

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

> CraigSA
>
[quoted text clipped - 45 lines]
> > >
> > > XL2002, WinXP

Reply to this Message

CraigSA - 18 May 2006 11:19 GMT

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 to this Message