Take the Median of a Dataset with Multiple Criteria

[novaquasar]

I'm struggling to take a median of a set of data with multiple criteria

I have a set of raw data (Shown Below)

I want to find the Median of the data in Row K (Gain) for all the cells where the DEF Call (Row H) is "Base Tommy" AND it's in Hole (Row R) 2 OR 3 OR 4 OR 5. (I don't want the median to include any other values of Row K where those criteria aren't met, i.e. Hole 7 or 8, Def Call "Arrow 4", )

Hypothetically, I want to be able to add new data to this worksheet and have Excel auto calculate the Median for all the data, including the new data, as long as it meets those criteria.

I've tried using Median(If( and Boolean logic but I'm admittedly stumped... Is there anyway for Excel to auto-calculate this for me (without needing to manually filter data and aggregating it? If so, how would I do this?

 

[kevin9999]

For future reference, it's better if you help those trying to help you by providing a sample of your data using the XL2BB add in, or alternatively making a copy of your file available via Dropbox, Google Sheets or similar file sharing platform - that way helpers aren't forced to type out your data to test various potential solutions. Does the following give you what you want (array formula entered with Ctrl+Shift+Enter)

Book1
	F	G	H	I	J	K	L	M	N	O	P	Q	R
1			DEF CALL			GAIN							HOLE
2	1		Arrow 4			4							2
3			Base Trump			5							2
4			Base Tommy			1							3
5			Base Trump			1							3
6			Leo 4			3							3
7			Base Tommy			5							3
8			Base Trump			8							3
9			Base Trump			8							3
10			Base Tommy			-1							4
11			Base Trump			1							4
12			Edge Auto			3							4
13			Base Trump			2							6
14			Base Tommy			3							6
15			Edge Auto			6							6
16			Base Trump			3							7
Sheet1

Cell Formulas
Range	Formula
F2	F2	=MEDIAN(IF((H2:H16="Base Tommy")*(R2:R16>=2)*(R2:R16<=5),K2:K16))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Another option

=MEDIAN(FILTER(K2:K100,(H2:H100="Base Tommy")*(R2:R100>=2)*(R2:R100<=5)))

 

[novaquasar]

Another option

=MEDIAN(FILTER(K2:K100,(H2:H100="Base Tommy")*(R2:R100>=2)*(R2:R100<=5)))

This showed up as an invalid function

 

[novaquasar]

For future reference, it's better if you help those trying to help you by providing a sample of your data using the XL2BB add in, or alternatively making a copy of your file available via Dropbox, Google Sheets or similar file sharing platform - that way helpers aren't forced to type out your data to test various potential solutions. Does the following give you what you want (array formula entered with Ctrl+Shift+Enter)

Book1
	F	G	H	I	J	K	L	M	N	O	P	Q	R
1			DEF CALL			GAIN							HOLE
2	1		Arrow 4			4							2
3			Base Trump			5							2
4			Base Tommy			1							3
5			Base Trump			1							3
6			Leo 4			3							3
7			Base Tommy			5							3
8			Base Trump			8							3
9			Base Trump			8							3
10			Base Tommy			-1							4
11			Base Trump			1							4
12			Edge Auto			3							4
13			Base Trump			2							6
14			Base Tommy			3							6
15			Edge Auto			6							6
16			Base Trump			3							7
Sheet1

Cell Formulas
Range	Formula
F2	F2	=MEDIAN(IF((H2:H16="Base Tommy")*(R2:R16>=2)*(R2:R16<=5),K2:K16))
Press CTRL+SHIFT+ENTER to enter array formulas.

This worked, thanks!

Is there a reason why Excel seems to prefer array functions for boolean logic rather than just letting me hit enter?

 

[Fluff]

This showed up as an invalid function

Are you using xl 2021 as per your profile?

 

[kevin9999]

This worked, thanks!

Is there a reason why Excel seems to prefer array functions for boolean logic rather than just letting me hit enter?

Not necessarily boolean logic - a simple IF() function is as boolean as you can get, but doesn't require entering as an array...