Both teams to score odds

[Patcheen]

i want to get the results chance in next game of both teams to score from last 5 games at HOME

im currently using this formula but its returning 200% even tho between them they have only scored 6 times so i imagine im looking for 60%

=SUM(INDEX(b:b,AGGREGATE(14,6,ROW(e5:e500)/(e5:e500="home"),SUM(INDEX(c:c,AGGREGATE(14,6,ROW(e5:e500)/(e5:e500="home"),SEQUENCE(5)))))))


I hope ye can make sence of what im trying to do

Thank you all in advance for help, suggestions and ideas

 

[JamesCanale]

MrExcelPlayground17.xlsx
	A	B	C	D	E	F	G
1	Date	Team A	Team B	blank	Location
2	3/17/2023	2	1		home		60%
3	3/18/2023	0	0		away
4	3/19/2023	1	2		home
5	3/20/2023	0	0		away
6	3/21/2023	2	3		home
7	3/22/2023	0	1		away
8	3/23/2023	1	0		home
9	3/24/2023	1	1		away
10	3/25/2023	1	1		home
11	3/26/2023	3	1		away
12	3/27/2023	1	2		home
13	3/28/2023	1	1		away
14	3/29/2023	0	2		home
15	3/30/2023	0	1		away
16	3/31/2023	0	0		home
17	4/1/2023	1	3		away
18	4/2/2023	0	1		home
19	4/3/2023	0	0		away
20	4/4/2023	1	5		home
21	4/5/2023	1	2		away
Sheet2

Cell Formulas
Range	Formula
G2	G2	=SUM(--(TAKE(FILTER(Table1[[Team A]:[Team B]],Table1[Location]="home"),-5)>0))/10
A3:A21	A3	=A2+1

 

[Patcheen]

This is what i get when i paste the formula is it poss to get a old time formula? thank you so much for your help

 

[JamesCanale]

Where my formula has "Table1[[Team A]:[Team B]}" and "Table1[Location]" you aught to change that reference to the cells in your data. You don't have a Table 1.

I don't know your row numbers, but you would just change the first one to maybe "B5:C24" and the other to "E5:E24"

 

[Fluff]

Similar idea

=SUM(SIGN(TAKE(FILTER(B5:C500,E5:E500="Home"),-5)))*0.1