MEDIAN IF or IFS

[sperbetsky]

Hello All,

Having some trouble here. I did find some Median If topics, but unfortunately I was unable to get it to work for my situation. I've tried Median with nested if statements and IFS. No luck. I'm trying to return the median value of a column of numbers if the date from another column falls within a range (quarterly). Essentially, I've got a year worth of data and I want to return the median value for each quarter. Hopefully that makes sense. Sorry, excel noob here

In the attached example I want to return the median of column A if Column B falls between two dates (beginning of the quarter and end of the quarter). Ideally, I'd like it to work so that I don't need to specify the number of rows each time as well (dynamic?)

Thank you in advance!

Scott

[joeu2004]

Array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:

=MEDIAN(IF($B$2:$B$1000>=E2, IF($B$2:$B$1000<=F2, $A$2:$A$1000)))

You should see curly-brackets ("{...}") in the Formula Bar.

That assumes that E2 contains your start date and F2 contains your end date.

Change the references to row 1000 to the most amount of data that you might reasonably expect to ever have. It is imprudent to use whole-column references like $B:$B, because Excel will look at all 1+ million rows and create temporary arrays of 1+ million entries.

[sperbetsky]

Thanks Joe!

[Sam Capricci]

Deleted. Wrong answer.

[Pete_UK]

MEDIAN is not the same as AVERAGE, though - perhaps you are thinking of MEAN.

Pete

[Sam Capricci]

sheesh, what was I thinking? I use these all the time and know that, Dope! thx Pete for the reminder.

that's what I get for multi-tasking.

[CARACALLA]

	A	B	C	D	E	F	G	H
1	Widgets	Date
2	5	1/1/2019				1/1/2019	7/1/2019	median
3	10	2/1/2019						15
4	15	3/1/2019
5	20	4/1/2019
6	30	5/1/2019
7	50	6/1/2019
8	1	7/1/2019
9	8	8/1/2019
10	15	9/1/2019
11	120	10/1/2019
12	90	11/1/2019
13	45	12/1/2019

Only Enter

H3=AGGREGATE(16,6,(A2:A1000)/(B2:B11000>=F2)/(B2:B1000<=G2),0.5)