Score conversion

[tigerzen]

Have a spreadsheet with the following values. The values represent the scores during a game. In cell B2, 1.2 represents our score at quarter time, 4.5 at half time, 5.6 at 3 quarter time and 10.11 at the final siren. The first digit represents goals and are worth 6 points, the second digit represents behinds and are worth 1 point. The scores in B2 then equate to 8, 29, 36, 71. My aim is to find the difference in points between teams by quarter so in the first case it would be 12 (20 - 8), 0 (29 - 29), 7 (43-36), -9 (62 - 71). Formula/function approach is preferred but will take anything that works.

Book3
	A	B	C
1	Opponent	Our Score	Their score
2	Team A	1.2 4.5 5.6 10.11	3.2 4.5 6.7 9.8
3	Team B	0.2 4.5 8.9 13.12	7.1 9.5 13.8 17.10
Sheet1

 

[JamesCanale]

MrExcelPlayground20.xlsx
	A	B	C	D	E	F	G
1	Opponent	Our Score	Their score	first	second	third	fourth
2	Team A	1.2 4.5 5.6 10.11	3.2 4.5 6.7 9.8	12	0	7	-9
3	Team B	0.2 4.5 8.9 13.12	7.1 9.5 13.8 17.10	41	30	29	22
Sheet6

Cell Formulas
Range	Formula
D2:G3	D2	=LET(x,B2,y,C2,a,VALUE(TEXTSPLIT(x,"."," ")),b,VALUE(TEXTSPLIT(y,"."," ")),c,TAKE(a,,1),cc,TAKE(a,,-1),ccc,6*c+cc,d,TAKE(b,,1),dd,TAKE(b,,-1),ddd,6*d+dd,TRANSPOSE(ddd-ccc))
Dynamic array formulas.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1	Opponent	Our Score	Their score
2	Team A	1.2 4.5 5.6 10.11	3.2 4.5 6.7 9.8	12 0 7 -9
3	Team B	0.2 4.5 8.9 13.12	7.1 9.5 13.8 17.10	41 30 29 22
Master

Cell Formulas
Range	Formula
D2:D3	D2	=TEXTJOIN(" ",,BYROW(TEXTSPLIT(C2,"."," ")*{6,1},LAMBDA(br,SUM(br)))-BYROW(TEXTSPLIT(B2,"."," ")*{6,1},LAMBDA(br,SUM(br))))

Or

Fluff.xlsm
	A	B	C	D	E	F	G
1	Opponent	Our Score	Their score
2	Team A	1.2 4.5 5.6 10.11	3.2 4.5 6.7 9.8	12	0	7	-9
3	Team B	0.2 4.5 8.9 13.12	7.1 9.5 13.8 17.10	41	30	29	22
Master

Cell Formulas
Range	Formula
D2:G3	D2	=TOROW(BYROW(TEXTSPLIT(C2,"."," ")*{6,1},LAMBDA(br,SUM(br)))-BYROW(TEXTSPLIT(B2,"."," ")*{6,1},LAMBDA(br,SUM(br))))
Dynamic array formulas.

 

[Peter_SSs]

.. or this?

23 08 23.xlsm
	A	B	C	D	E	F	G
1	Opponent	Our Score	Their score	Q1	Q2	Q3	Q4
2	Team A	1.2 4.5 5.6 10.11	3.2 4.5 6.7 9.8	12	0	7	-9
3	Team B	0.2 4.5 8.9 13.12	7.1 9.5 13.8 17.10	41	30	29	22
4	Team C	7.1 9.5 13.8 17.10	0.2 4.5 8.9 13.12	-41	-30	-29	-22
AFL Scores

Cell Formulas
Range	Formula
D2:G4	D2	=LET(a,TEXTSPLIT(C2," "),h,TEXTSPLIT(B2," "),INT(a)*6+TEXTAFTER(a,".")-(INT(h)*6+TEXTAFTER(h,".")))
Dynamic array formulas.

 

[tigerzen]

Thanks so much guys and appreciate the options provided.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

 

[Georgiboy]

Another similar option:

Book1
	A	B	C	D	E	F	G
1	Opponent	Our Score	Their score
2	Team A	1.2 4.5 5.6 10.11	3.2 4.5 6.7 9.8	12	0	7	-9
3	Team B	0.2 4.5 8.9 13.12	7.1 9.5 13.8 17.10	41	30	29	22
Sheet1

Cell Formulas
Range	Formula
D2:G3	D2	=LET(ts,TEXTSPLIT(C2,"."," ")-TEXTSPLIT(B2,"."," "),TOROW(INDEX(ts,,1)*6+INDEX(ts,,2)))
Dynamic array formulas.