Median Formula

[robfl22]

I have the game scores from every nba game this season in a table. I am trying to figure out the MEDIAN for each NBA team. I know how to get each teams Away Median Score and Home Median Score by using =MEDIAN(IF(nba_2122_schedule[Away Team]=SDs!A3,IF(ISNUMBER(nba_2122_schedule[Away Score]),nba_2122_schedule[Away Score]))). However, what I cannot figure out is to how to get an OVERALL MEDIAN . any help would be appreciated.

 

[JGordon11]

The total median formula assumes a score of 0 will not occur. If there is a 0 score it will calculate incorrectly.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Away Team	Home Team	Away Score	Home Score
2	2/5/2022	A	D	88	124					using LET function	without LET function
3	2/2/2022	C	D	102	117		Team	Away Median	Home Median	Total Median	Total Median
4	2/5/2022	B	C	110	91		A	88	89	88.5	88.5
5	1/31/2022	C	A	87	89		B	106.5	131.5	119.5	119.5
6	2/5/2022	A	B	138	129		C	94.5	101	96.5	96.5
7	1/29/2022	B	D	103	101		D	141	117	120.5	120.5
8	2/5/2022	D	C	141	111
9	1/27/2022	A	B	82	134
10
Sheet3

Cell Formulas
Range	Formula
H4:H7	H4	=MEDIAN(IF(Table1[Away Team]=$G4,Table1[Away Score],""))
I4:I7	I4	=MEDIAN(IF(Table1[Home Team]=$G4,Table1[Home Score],""))
J4:J7	J4	=LET(s,(Table1[Away Team]=$G4)*Table1[Away Score]+(Table1[Home Team]=$G4)*Table1[Home Score],MEDIAN( IF(s,s,"")))
K4:K7	K4	=MEDIAN( IF((Table1[Away Team]=$G4)*Table1[Away Score]+(Table1[Home Team]=$G4)*Table1[Home Score],(Table1[Away Team]=$G4)*Table1[Away Score]+(Table1[Home Team]=$G4)*Table1[Home Score],""))

 

[robfl22]

The total median formula assumes a score of 0 will not occur. If there is a 0 score it will calculate incorrectly.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Away Team	Home Team	Away Score	Home Score
2	2/5/2022	A	D	88	124					using LET function	without LET function
3	2/2/2022	C	D	102	117		Team	Away Median	Home Median	Total Median	Total Median
4	2/5/2022	B	C	110	91		A	88	89	88.5	88.5
5	1/31/2022	C	A	87	89		B	106.5	131.5	119.5	119.5
6	2/5/2022	A	B	138	129		C	94.5	101	96.5	96.5
7	1/29/2022	B	D	103	101		D	141	117	120.5	120.5
8	2/5/2022	D	C	141	111
9	1/27/2022	A	B	82	134
10
Sheet3

Cell Formulas
Range	Formula
H4:H7	H4	=MEDIAN(IF(Table1[Away Team]=$G4,Table1[Away Score],""))
I4:I7	I4	=MEDIAN(IF(Table1[Home Team]=$G4,Table1[Home Score],""))
J4:J7	J4	=LET(s,(Table1[Away Team]=$G4)*Table1[Away Score]+(Table1[Home Team]=$G4)*Table1[Home Score],MEDIAN( IF(s,s,"")))
K4:K7	K4	=MEDIAN( IF((Table1[Away Team]=$G4)*Table1[Away Score]+(Table1[Home Team]=$G4)*Table1[Home Score],(Table1[Away Team]=$G4)*Table1[Away Score]+(Table1[Home Team]=$G4)*Table1[Home Score],""))

Works Perfectly....Thank you so much!