Valid Matching Outcomes

[cotech_10]

I would like to request the following task possibly to be done using excel formulas:

I have 7 possible numbers in Table 1 and would like to do a comparison analysis
with the numbers in Table 2. As per the image below:

In Table 2 there are 12 positions I would like to perform alternate analysis
based on Positions.

Step 1:

I would like to compare Positions 1,3,5,7,9,11 with all of the number values in Table 1
and count the number of match occurrences with those numbers.

For eg: From Table 2 the following Position Number values are:

Position 1=200
Position 3=330
Position 5=440
Position 7=520
Position 9=620
Position 11=730

According to Match Rule for these Positions we are only concerned with the outcomes where there is a valid
match outcome.

Position 1=200 MATCHES with one of the numbers in TABLE 1 which is a valid outcome
Position 3=330 does not match with any of the numbers in TABLE 1 which is not a valid outcome
Position 5=440 does not match with any of the numbers in TABLE 1 which is not a valid outcome
Position 7=520 does not match with any of the numbers in TABLE 1 which is not a valid outcome
Position 9=620 does not match with any of the numbers in TABLE 1 which is not a valid outcome
Position 11=730 does not match with any of the numbers in TABLE 1 which is not a valid outcome

The only match value is Position 1

Table 1 = 200 Table 2 = 200

Therefore the match count = 1

STEP 2:

The second part of the calculation and to be included in the Total Count is as follows:

I would like to compare Positions 2,4,6,8,10,12 with all of the number values in Table 1
and count the number of NON-MATCH occurrences with those numbers.

For eg: From Table 2 the following Position Number values are:


Position 2=240
Position 4=350
Position 6=450
Position 8=550
Position 10=650
Position 12=800


According to Match Rule we are only concerned with the outcomes where there is a valid
NON-MATCH outcome.


For eg:

Position 2=240 does not match with any of the numbers in TABLE 1 which is a valid outcome
Position 4=350 does not match with any of the numbers in TABLE 1 which is a valid outcome
Position 6=450 does not match with any of the numbers in TABLE 1 which is a valid outcome
Position 8=550 does not match with any of the numbers in TABLE 1 which is a valid outcome
Position 10=650 does not match with any of the numbers in TABLE 1 which is a valid outcome
Position 12=800 does MATCH with one of the numbers in TABLE 1 which is a non-valid outcome


The Matching Outcomes are Positions 2,4,6,8 & 10 and NOT Position 12 as the rule
specifies that non-matching conditions are to be met.


The number of outcomes that satisfy this rule = 5


The Totals for these (2) levels of analysis is 6.

1. STEP 1 = 1
STEP 2 = 5

Total = 6 which is the value placed in the OUTPUT Table


I look forward in hearing back from someone soon



Regards

 

[Peter_SSs]

Here is another one that you could try if you want. Not sure if you will need Ctrl+Shift+Enter with your version.

=-SUM(-ISNUMBER(MATCH(INDEX(B6:M6,{1,3,5,7,9,11}),B$2:H$2,0)),-ISNA(MATCH(INDEX(B6:M6,{2,4,6,8,10,12}),B$2:H$2,0)))

 

[cotech_10]

Here is another one that you could try if you want. Not sure if you will need Ctrl+Shift+Enter with your version.

=-SUM(-ISNUMBER(MATCH(INDEX(B6:M6,{1,3,5,7,9,11}),B$2:H$2,0)),-ISNA(MATCH(INDEX(B6:M6,{2,4,6,8,10,12}),B$2:H$2,0)))

Hi Peter,

Once again thanks for this solution, much appreciated.

 

[cotech_10]

Hi Peter,

Once again thanks for this solution, much appreciated.

Hi Peter,

Just clarification if I could, the result output I am getting is a different value output to what I was expecting

The result value is = 2 where it should = 6.



Cheers

 

[cotech_10]

Hi Peter,

Just clarification if I could, the result output I am getting is a different value output to what I was expecting

The result value is = 2 where it should = 6.



Cheers

Hi Peter,

I am using EXCEL 2016..

 

[Peter_SSs]

I am using EXCEL 2016..

I don't have that version to test. Still, it seems you have one or more formulas already that seem to be working for you.

 

[cotech_10]

I don't have that version to test. Still, it seems you have one or more formulas already that seem to be working for you.

Hi Peter,

You are absolutely right, I have working solutions.