Help needed for potential formula

[Relaxation_Study]

Hi,
I have a question, I am a teacher and I have been sent students mock exam answers in excel. The way they are presented are as a sequence of numbers, every number in the sequence that is correctly placed gains the student a mark. The correct answer is also inversed so 9,8,7,6,5,4,3,2,1 would be a total of 9 marks. The sequences are all in a single cell as well. Is there a formula that I can use to match the location of the number to see if its in the correct place and then give a score accordingly or will I have to count all this out manually? I am a bit of a novice at Excel so I have the excel sheet for more clarity.

help.csv
	A	B	C	D	E	F	G	H	I	J	K
2	["blk3_3","blk2_3","blk1_3"]
3	["blk3_3","blk2_3","blk1_3"]							I need a formula that essentially can identify if these are in the correct position. That postion is an inverse countdown so 3,2,1 would be worth 3 point 1,2,3 would be worth 1 point & 2,1,3 would be worth 0 points.
4	["blk3_3","blk2_3","blk1_3"]
5	["blk4_4","blk3_4","blk2_4","blk1_4"]							Each cell has a unique sequence in.
6	["blk4_4","blk3_4","blk2_4","blk1_4"]
7	["blk4_4","blk3_4","blk2_4","blk1_4"]
8	["blk5_5","blk4_5","blk3_5","blk2_5","blk1_5"]
9	["blk5_5","blk4_5","blk3_5","blk2_5","blk1_5"]
10	["blk5_5","blk4_5","blk3_5","blk2_5","blk1_5"]
11	["blk6_6","blk5_6","blk4_6","blk3_6","blk2_6","blk1_6"]
12	["blk6_6","blk5_6","blk4_6","blk3_6","blk2_6","blk1_6"]
13	["blk6_6","blk5_6","blk4_6","blk3_6","blk1_6","blk2_6"]
14	["blk7_7","blk6_7","blk5_7","blk4_7","blk3_7","blk2_7","blk1_7"]
15	["blk7_7","blk6_7","blk5_7","blk4_7","blk3_7","blk2_7","blk1_7"]
16	["blk7_7","blk6_7","blk5_7","blk4_7","blk3_7","blk2_7","blk1_7"]
17	["blk8_8","blk7_8","blk6_8","blk5_8","blk3_8","blk4_8","blk1_8","blk2_8"]
18	["blk8_8","blk7_8","blk6_8","blk4_8","blk5_8","blk3_8","blk2_8","blk1_8"]
19	["blk8_8","blk7_8","blk6_8","blk5_8","blk3_8","blk4_8","blk2_8","blk1_8"]
20	["blk9_9","blk8_9","blk7_9","blk6_9","blk5_9","blk4_9","blk3_9","blk2_9","blk1_9"]
21	["blk9_9","blk8_9","blk7_9","blk6_9","blk5_9","blk4_9","blk1_9","blk2_9","blk3_9"]
22	["blk9_9","blk8_9","blk7_9","blk6_9","blk5_9","blk4_9","blk3_9","blk1_9","blk2_9"]
23
help

 

[Peter_SSs]

Welcome to the MrExcel board!

Can you enter the results you expect for that sample data, post the mini-sheet again with those results as well and explain in detail how you manually get a few of those results?
I am unsure what numbers I should be looking at in those rows of data.

 

[Relaxation_Study]

Hi thanks for the response, manually what I have to do is see if the number is in the correct position in the sequence. The correct answer is always counting inversely, so as you can see in the first cell, ["blk3_3","blk2_3","blk1_3"], I would check if left to right the number is decreasing. The BLK(X) is the relevant number the number at the end just states how long the total sequence is. So for the example linked, BLK3 is in the first position so that's correct, BLK2 is in the second position so that's correct and BLK1 is in the third position so that's correct. Every BLK(X) in the correct position is worth one mark, so the example sequence I sent would score a total of 3. I have linked the minisheet with the scores of the sequence to the right. If you need anymore information let me know.

help.csv
	A	B	C	D	E	F	G	H	I	J
2	["blk3_3","blk2_3","blk1_3"]			<---	Score=3
3	["blk3_3","blk2_3","blk1_3"]			<---	Score=3
4	["blk3_3","blk2_3","blk1_3"]			<---	Score=3
5	["blk4_4","blk3_4","blk2_4","blk1_4"]				Score=4
6	["blk4_4","blk3_4","blk2_4","blk1_4"]				Score = 4
7	["blk4_4","blk3_4","blk2_4","blk1_4"]				Score = 4
8	["blk5_5","blk4_5","blk3_5","blk2_5","blk1_5"]					Score = 5
9	["blk5_5","blk4_5","blk3_5","blk2_5","blk1_5"]					Score = 5
10	["blk5_5","blk4_5","blk3_5","blk2_5","blk1_5"]					Score = 5
11	["blk6_6","blk5_6","blk4_6","blk3_6","blk2_6","blk1_6"]						Score = 6
12	["blk6_6","blk5_6","blk4_6","blk3_6","blk2_6","blk1_6"]						Score = 6
13	["blk6_6","blk5_6","blk4_6","blk3_6","blk1_6","blk2_6"]						Score = 4
14	["blk7_7","blk6_7","blk5_7","blk4_7","blk3_7","blk2_7","blk1_7"]							Score = 7
15	["blk7_7","blk6_7","blk5_7","blk4_7","blk3_7","blk2_7","blk1_7"]							Score = 7
16	["blk7_7","blk6_7","blk5_7","blk4_7","blk3_7","blk2_7","blk1_7"]							Score = 7
17	["blk8_8","blk7_8","blk6_8","blk5_8","blk3_8","blk4_8","blk1_8","blk2_8"]								Score = 4
18	["blk8_8","blk7_8","blk6_8","blk4_8","blk5_8","blk3_8","blk2_8","blk1_8"]								Score = 6
19	["blk8_8","blk7_8","blk6_8","blk5_8","blk3_8","blk4_8","blk2_8","blk1_8"]								Score = 6
20	["blk9_9","blk8_9","blk7_9","blk6_9","blk5_9","blk4_9","blk3_9","blk2_9","blk1_9"]								Score = 9
21	["blk9_9","blk8_9","blk7_9","blk6_9","blk5_9","blk4_9","blk1_9","blk2_9","blk3_9"]								Score = 7
22	["blk9_9","blk8_9","blk7_9","blk6_9","blk5_9","blk4_9","blk3_9","blk1_9","blk2_9"]								Score = 7
23
24
25
help

 

[Peter_SSs]

Thanks for the samples and further explanation.
If the numbers of interest are all single digit (that is, maximum of 9) like your samples, then you could try this.

I have removed some of the sample rows as there were quite a few duplicates (which don't add further clarity)

Relaxation_Study.xlsm
	A	B	C	D	E	F	G	H
1		Score
2	["blk3_3","blk2_3","blk1_3"]	3	<---	Score=3
3	["blk4_4","blk3_4","blk2_4","blk1_4"]	4		Score=4
4	["blk5_5","blk4_5","blk3_5","blk2_5","blk1_5"]	5			Score = 5
5	["blk6_6","blk5_6","blk4_6","blk3_6","blk2_6","blk1_6"]	6				Score = 6
6	["blk6_6","blk5_6","blk4_6","blk3_6","blk1_6","blk2_6"]	4				Score = 4
7	["blk7_7","blk6_7","blk5_7","blk4_7","blk3_7","blk2_7","blk1_7"]	7					Score = 7
8	["blk8_8","blk7_8","blk6_8","blk5_8","blk3_8","blk4_8","blk1_8","blk2_8"]	4						Score = 4
9	["blk8_8","blk7_8","blk6_8","blk4_8","blk5_8","blk3_8","blk2_8","blk1_8"]	6						Score = 6
10	["blk8_8","blk7_8","blk6_8","blk5_8","blk3_8","blk4_8","blk2_8","blk1_8"]	6						Score = 6
11	["blk9_9","blk8_9","blk7_9","blk6_9","blk5_9","blk4_9","blk3_9","blk2_9","blk1_9"]	9						Score = 9
12	["blk9_9","blk8_9","blk7_9","blk6_9","blk5_9","blk4_9","blk1_9","blk2_9","blk3_9"]	7						Score = 7
13	["blk9_9","blk8_9","blk7_9","blk6_9","blk5_9","blk4_9","blk3_9","blk1_9","blk2_9"]	7						Score = 7
Sheet1 (2)

Cell Formulas
Range	Formula
B2:B13	B2	=LET(n,LEN(A2)-LEN(SUBSTITUTE(A2,"_","")),seq,SEQUENCE(,n,n,-1),SUMPRODUCT(--(n+1-FIND(seq,CONCAT(MID(A2,SEQUENCE(,n,6,9),1)))=seq)))

 

[Relaxation_Study]

Thank you so much, it works! You just saved me many hours of counting!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.