calculate array

[keda duck]

This is a problem I encountered in my work. I want to use formula to solve this problem

Each cell is two numbers separated by character "/", and the number may contain Decimal separator.
The cells may be empty.
B3 and B2 are compared and assigned scores, C3 and C2 are compared and assigned scores...
Finally, the sum of the assigned scores for each row and the second row is calculated and written in column W for each row.

Scoring rules:
2: Two cells are the same and neither is empty
1: A certain number in cell B3 is the same as two numbers in cell B2
0: At least one of the two cells is empty, or the two numbers in cell B3 are different from the two numbers in cell B2

Taking the sixth row as an example.
B6 is empty, so its score is 0.
the first number of c6 is 9, and the second number of c4 is 13, these numbers are not existed in c2, so its score is 0.
The first number of d6 is 9, and the second number of b2 is 12, 12 is existed in b2 but 9 is not existed in, so its score is 1.
……
the first number of j6 is 8, and the second number of j6 is 11, 8 and 11 are both in j2, so its score is 2.
……
and so on.

When I calculate the score for each cell, I sum the scores for each row.

calculate.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	no.	data 1	data 2	data 3	data 4	data 5	data 6	data 7	data 8	data 9	data 10	data 11	data 12	data 13	data 14	data 15	data 16	data 17	data 18	data 19	data 20	data 21
2	1	16/16	8/8	11/12	9/9	25.2/30.2	12/15	10/11	30/30	8/11	16/17.3	14/14	11/15	14/14	10/11	18/27	17/24	16/20	9/9	12/21	11/12	24/24
3	2	15/17	11/12	12/12	11/13	19/28.2	13/15	11/12	30/31	8/8	13/18.3	13/14	15/17	12/13	14/14	17/23	19/23	16/20	7/7	15/17	10/11	22/22
4	3	15/16	10/11	8/10	11/13	22/28.2	15/16	7/11	30/32	8/11	14/15	12/15.2	11/15	10/14	11/14	19/23	24/25	15/19	6/9	15/15	12/12	21/22
5	4	17/17	9/10	8/10	10/11	20/27.2	12/14	10/11	29/31.2	8/8	11/14	13/15	13/18	11/11	10/14	18/20	17/22	14/17	7/9	15/15	8/11	22/23
6	5		9/13	9/12	11/11	25.2/26.2	13/13	11/12	29/30	8/11	12/15	13/15.2	16/16	12/13	10/12	19/19	16/20	14/18	6/9	15/16	9/14	24/24
7	6	15/16	10/12	8/8	12/13	19.2/24.2	15/16	11/11	29/32.2	8/8	12/13	13/13	15/17	14/15	11/14	21/23	23/24	14/19	9/9	14/20	11/12	23/25
8	7	15/16	8/11	8/11	12/13	22/24.2	15/16	11/11	31.2/32	8/8	13/14	13/15.2	15/16	10/15	11/12	18/23	22/24	17/19	7/9	14/15	10/12	22/23
9	8	14/18	9/11	12/13	9/14	25.2/28.2	16/16	11/12	32/32.2	8/9	15/16	13/14	17/17	10/12	10/14	19/21	16/23	14/15	7/8	13/14	12/12	24/25
10	9	15/17	8/11	8/11	11/11	22.2/28.2	14/15	10/12	30/31	8/8	17/17	13.2/14.2	11/15	11/14	11.3/12	18/19	20/23	17/18	7/9	13/20	12/12	24/25
11	10	15/17	8/8	8/13	11/12	19/28.2		10/10	29/30	8/8	15/17	13/14.2	11/11	11/12	10/11.3	18/20	19/20	17/18	9/9	13/14	12/12	23/25
12	11	17/18	9/11	11/11		22.2/28.2	15/15	12/12	29/31	8/10		13.2/14	15/15	13/14	11/12	19/21		14/17	7/9	14/20	11/12	21/24
13	12	14/16	10/11	9/12	9/11	19/26.2	13/14	10/13	29/31.2	8/8	15/15	13/14	15/16	11/14		18/19	23/24	14/16	9/9	13/16	10/10	24/25
14	13	15/16		12/13	9/11	19/27.2	13/14	11/12	30/31.2	8/8	15/15	13/14.2	15/16	11/14	10/12	18/19	19/23	14/18	7/9	13/13	10/12	23/25
15	14	15/15	10/11	12/13	10/10	19/27.2	13/14	11/12	29/31.2	8/10	15/15	13/14	11/15	11/13	12/12	18/19	19/23	14/16	9/9	13/16	10/12	23/24
16	15	15/16	10/12	10/11	9/10	28.2/29.2	13/14	11/12	29/29	8/9	13/15	13/13	11/17	13/15	10/11	21/22	19/23	16/19	6/7	14/18	11/12	23/24
17	16	16/18	9/12	8/11	11/12	19/21	13/15	12/13	32/33.2	11/11	11/14	13/14.2	11/15	13/16	10/11	17/18	19/20	18/19	7/8	16/23	10/12	23/24
18	17	15/15	8/9	8/13	11/12	23.2/28.2	13/13	11/13	30/30	9/11	14/17	14.2/14.2	15/16	13/14	10/11	18/19	24/24	14/18	9/10	13/13	10/12	19/20
19	18	16/17	8/11	11/11	12/12	20/25.2	13/16	9/12	30/31.2	10/11	12/15	12.2/15.2	15/16	12/14	10/10	18/20	19/20	16/18	7/7	13/14	12/13	20/23
20	19	14/15	9/11	9/13	9/10	19/30.2	13/14	12/13	31.2/33.2	8/10	15/15		11/16	13/14	10/12	19/19	19/24	14/17	7/9	13/16	10/12	24/25
21	20	15/16	8/10	12/12	10/11	26.2/27.2	13/13	10/11	29/30	8/8	13/15	13/14	11/15	11/11	10/12	18/18	23/23	16/18	9/9	13/16	10/12	23/24
22	21		10/13	8/10	12/12	19/30.2	14/15	12/13	29/30	8/8	15/15	15/16.2	11/16	12/13	11/14	18/19	19/23	16/19	9/9	15/16	10/12	22/23
Sheet1 (2)

 

[nemmi69]

Is the cell just compare to the one above it? If so, is it both values of that cell or the same side only?

 

[keda duck]

Is the cell just compare to the one above it? If so, is it both values of that cell or the same side only?

The cell just Compare the corresponding cells in the second row. It compare both values to that cell.

 

[keda duck]

I found some errors in the example, correct them as follows:
Taking the sixth row as an example.
B6 is empty, so its score is 0.
the first number of c6 is 9, and the second number of c6 is 13, these numbers are not existed in c2, so its score is 0.
The first number of d6 is 9, and the second number of d6 is 12, 12 is existed in d2 but 9 is not existed in, so its score is 1.
……
the first number of j6 is 8, and the second number of j6 is 11, 8 and 11 are both in j2, so its score is 2.
……
and so on.

When I calculate the score for each cell, I sum the scores for each row.

 

[nemmi69]

So all rows only compare to row2 in same column?

 

[keda duck]

So all rows only compare to row2 in same column?

Yes. Can you solve this?

 

[Peter_SSs]

What is the score for this cell 2 or 1?

23 07 12.xlsm
	J
1	data 9
2	8/11
22	8/8
Score rows

 

[keda duck]

What is the score for this cell 2 or 1?

23 07 12.xlsm
	J
1	data 9
2	8/11
22	8/8
Score rows

1

 

[Peter_SSs]

1

What about the other way around then? 2 or 1?

23 07 12.xlsm
	I
1	data 8
2	30/30
6	29/30
Score rows

 

[keda duck]

What about the other way around then? 2 or 1?

23 07 12.xlsm
	I
1	data 8
2	30/30
6	29/30
Score rows

1 too.