Rugby - Biggest Win

[chorina13]

Hi, Like a lot of others, I am trying to create my own local Rugby Spreadsheet, but but struck a problem with the formula. On one sheet which is named 'Setup' I have a small box containing some facts (shown below). I am okay with the first part getting the Home and Away teams names, but having trouble with the formula to return the score value in the colored boxes. The other picture show the data on another sheet named 'Calcs'.
Can anyone help

Thanks.
Have a GREAT day
Blackie

 

[StephenCrump]

Welcome to the Forum!

It's not clear from this example what results you're expecting. Are you looking for Taranaki's biggest home win? Or the biggest home win out of all 36 games, which might involve Taranaki?

What does biggest mean? Biggest home score? Biggest difference between home and away score? What if there is more than one equal result?

 

[chorina13]

Welcome to the Forum!

It's not clear from this example what results you're expecting. Are you looking for Taranaki's biggest home win? Or the biggest home win out of all 36 games, which might involve Taranaki?

What does biggest mean? Biggest home score? Biggest difference between home and away score? What if there is more than one equal result?

Thanks for your response. Admittedly, the example shown is just only one result, but what I am after is that at the end of the season, I want the result to show the team who scored the highest points for any Home game over the season along with what the score was and who it was against. This is cover both the Home game and Away game which would be updated automatically as the season progresses. I hope the better clarifys what it is I am after.
Have a GREAT Easter
Blackie

 

[StephenCrump]

Perhaps something like this:

	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC
1							Game	Home team	HScore	AScore	Away Team
2	Home score	Home	Away	Score			1	A	27	27	F
3	Highest	A	F	27	27		2	B	10	19	G
4		C	H	27	28		3	C	27	28	H
5							4	D	14	25	I
6	Highest winning	F	B	23	14		5	E	10	15	J
7							6	F	23	14	B
8							7	G	21	7	C
9							8	H	18	10	D
10							9	I	19	13	E
11							10	J	20	11	A
12							11	A	13	17	H
13							12	B	17	16	I
14							13	C	20	10	J
15							14	D	9	11	F
16							15	E	20	3	G
17							16	F	22	9	E
18							17	G	13	12	A
19							18	H	11	17	B
20							19	I	14	17	C
21							20	J	12	25	D
22
Sheet1

Cell Formulas
Range	Formula
AT3:AW4	AT3	=CHOOSECOLS(FILTER(AZ2:BC21,BA2:BA21=MAX(BA2:BA21)),{1,4,2,3})
AT6:AW6	AT6	=IFERROR(CHOOSECOLS(FILTER(AZ2:BC21,BA2:BA21=MAX(IF(BA2:BA21-BB2:BB21>0,BA2:BA21))),{1,4,2,3}),"No home wins!")
Dynamic array formulas.

 

[chorina13]

Perhaps something like this:

	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC
1							Game	Home team	HScore	AScore	Away Team
2	Home score	Home	Away	Score			1	A	27	27	F
3	Highest	A	F	27	27		2	B	10	19	G
4		C	H	27	28		3	C	27	28	H
5							4	D	14	25	I
6	Highest winning	F	B	23	14		5	E	10	15	J
7							6	F	23	14	B
8							7	G	21	7	C
9							8	H	18	10	D
10							9	I	19	13	E
11							10	J	20	11	A
12							11	A	13	17	H
13							12	B	17	16	I
14							13	C	20	10	J
15							14	D	9	11	F
16							15	E	20	3	G
17							16	F	22	9	E
18							17	G	13	12	A
19							18	H	11	17	B
20							19	I	14	17	C
21							20	J	12	25	D
22
Sheet1

Cell Formulas
Range	Formula
AT3:AW4	AT3	=CHOOSECOLS(FILTER(AZ2:BC21,BA2:BA21=MAX(BA2:BA21)),{1,4,2,3})
AT6:AW6	AT6	=IFERROR(CHOOSECOLS(FILTER(AZ2:BC21,BA2:BA21=MAX(IF(BA2:BA21-BB2:BB21>0,BA2:BA21))),{1,4,2,3}),"No home wins!")
Dynamic array formulas.

Again, thank you for your help. but after entering the formulas, all I got was #NAME? in AT3 and No home wins! in AT6. I double & tripple checked that the formulas I entered were as you sent.
For your info the formulas I used in my biggest winner etc box are as follows which may be of assistance to you.

Biggest Home Win was quote =index(DummyTable!$AZ$3:$AZ$80,MATCH(MAX(DummyTable!$BA$3:$BA$80),DummyTable!$BA$3:$BA$80,0),1) unqote.
and against the Away Team quote =index(DummyTable!$BC$3:$BC$80,MATCH(MAX(DummyTable!$BB$3:$BB$80),DummyTable!$BB$3:$BB$80,0),1) unqote.

Weather this helps or not I don't know, but all I am trying to do is to get the appropriate teams points into those boxes.

Thank you and have a great day.

 

[StephenCrump]

Oops sorry, you won't have the CHOOSECOLS function in Excel 2021.

Try this:

	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC
1		Home	Home	Away	Away		Game	Home team	HScore	AScore	Away Team
2	Home score	Team	Score	Score	Team		1	A	27	27	F
3	Highest	A	27	27	F		2	B	10	19	G
4		C	27	28	H		3	C	27	28	H
5							4	D	14	25	I
6	Highest winning	F	23	14	B		5	E	10	15	J
7							6	F	23	14	B
8							7	G	21	7	C
9							8	H	18	10	D
10							9	I	19	13	E
11							10	J	20	11	A
12							11	A	13	17	H
13							12	B	17	16	I
14							13	C	20	10	J
15							14	D	9	11	F
16							15	E	20	3	G
17							16	F	22	9	E
18							17	G	13	12	A
19							18	H	11	17	B
20							19	I	14	17	C
21							20	J	12	25	D
Sheet1

Cell Formulas
Range	Formula
AT3:AW4	AT3	=FILTER(AZ2:BC21,BA2:BA21=MAX(BA2:BA21))
AT6:AW6	AT6	=IFERROR(FILTER(AZ2:BC21,(BA2:BA21>0)*(BA2:BA21=MAX(IF(BA2:BA21-BB2:BB21>0,BA2:BA21)))),"No home wins!")
Dynamic array formulas.

 

[chorina13]

Oops sorry, you won't have the CHOOSECOLS function in Excel 2021.

Try this:

	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC
1		Home	Home	Away	Away		Game	Home team	HScore	AScore	Away Team
2	Home score	Team	Score	Score	Team		1	A	27	27	F
3	Highest	A	27	27	F		2	B	10	19	G
4		C	27	28	H		3	C	27	28	H
5							4	D	14	25	I
6	Highest winning	F	23	14	B		5	E	10	15	J
7							6	F	23	14	B
8							7	G	21	7	C
9							8	H	18	10	D
10							9	I	19	13	E
11							10	J	20	11	A
12							11	A	13	17	H
13							12	B	17	16	I
14							13	C	20	10	J
15							14	D	9	11	F
16							15	E	20	3	G
17							16	F	22	9	E
18							17	G	13	12	A
19							18	H	11	17	B
20							19	I	14	17	C
21							20	J	12	25	D
Sheet1

Cell Formulas
Range	Formula
AT3:AW4	AT3	=FILTER(AZ2:BC21,BA2:BA21=MAX(BA2:BA21))
AT6:AW6	AT6	=IFERROR(FILTER(AZ2:BC21,(BA2:BA21>0)*(BA2:BA21=MAX(IF(BA2:BA21-BB2:BB21>0,BA2:BA21)))),"No home wins!")
Dynamic array formulas.

Well that certainly made a difference - but the results are exactly the same - what I am looking for is the biggest Home win and the biggest Away win.
Thanks agai
Blackie

 

[StephenCrump]

I want the result to show the team who scored the highest points for any Home game over the season along with what the score was and who it was against.

... but the results are exactly the same - what I am looking for is the biggest Home win and the biggest Away win.

Yes, I have given you two results for the home score, to illustrate that "biggest home win" is not necessarily the same as "highest points for any home game".

Pick the formula you want, and it should be very simple to adapt to the away game result.

 

[chorina13]

Yes, I have given you two results for the home score, to illustrate that "biggest home win" is not necessarily the same as "highest points for any home game".

Pick the formula you want, and it should be very simple to adapt to the away game result.

Thank you very much - you have been a great help
Have a great day
Blackie