Finding the median from a date list dinamically

[YuriHR]

Hello

I'm having trouble with an apparent easy task, but I haven't found any reasonable solution.

In the column A are the dates, while in the column B are the values. For each day there's usually more than one value. I'm building a report with descriptive statistics, and it's working fine for the values, but I can't find a way to represent the median dynamically for the number of daily operations. For instance:

April 19: 8 rows; April 20: 4 rows; April 21: 5 rows; April 22: 3 rows ... In this case the median would be 4,5.

What I'd like to do is to find the median for the number of rows (operations) per day changing the date range dynamically, without using another column or sheet for help, only with formula.

(I tried a lot of array combinations with MEDIAN, IF, FREQUENCY and COUNTIF. Said formulas worked well for the values, but I really do not know what's going on with the dates.)

Thanks in advance!

 

[jasonb75]

If your dates are chronological then I would suggest a formula on the basis of (this will definitely work)

=MEDIAN(INDEX(values range,MATCH(starting date, date range,0)):INDEX(values range,MATCH(ending date, date range,0)))

If the dates are not chronological then possibly an array confirmed formula like (this one might work, without testing I'm not certain that it is 100% correct).

=MEDIAN(IF(date range >= starting date,IF(date range <= ending date,values)))

 

[YuriHR]

Thanks for your help, Jason!

Unfortunately, my problem is related to the median of the numbers of rows by day, though. For the values I used your last formula (ideally the dates would be chronological, but they're not) and had success.

in my first post, I mentioned the number of rows by day. Ideally, the formula would count the number of rows according to the range I'd choose, calculate the number of rows by day (from an array, I presume), and return the median of rows by day. It would be totally based on the column A.

Thanks again.

 

[jasonb75]

Could you give a slightly bigger example, with the dates out of order and the results you need based on a couple of date ranges.
If you click the XL2BB button on the reply toolbar that will take you to an add-in that you can use to post a section of your sheet to the forum.

 

[sandy666]

is that what you want?

Date	Value	Countif		Date	Median
19/04/2020	1	1		19/04/2020	4.5
19/04/2020	2	2		20/04/2020	2.5
19/04/2020	3	3		21/04/2020	3
19/04/2020	4	4		22/04/2020	2
19/04/2020	5	5
19/04/2020	6	6
19/04/2020	7	7
19/04/2020	8	8
20/04/2020	9	1
20/04/2020	10	2
20/04/2020	11	3
20/04/2020	12	4
21/04/2020	13	1
21/04/2020	14	2
21/04/2020	15	3
21/04/2020	16	4
21/04/2020	17	5
22/04/2020	18	1
22/04/2020	19	2
22/04/2020	20	3

 

[AhoyNC]

Something like this:
Array formula that must be entered with CTRL-SHIFT-ENTER.

Book1
	A	B	C	D
1	4/19/2020
2	4/19/2020		Median=	4.5
3	4/19/2020
4	4/19/2020
5	4/19/2020
6	4/19/2020
7	4/19/2020
8	4/19/2020
9	4/20/2020
10	4/20/2020
11	4/20/2020
12	4/20/2020
13	4/21/2020
14	4/21/2020
15	4/21/2020
16	4/21/2020
17	4/21/2020
18	4/22/2020
19	4/22/2020
20	4/22/2020
Sheet2

Cell Formulas
Range	Formula
D2	D2	{=MEDIAN(IF(FREQUENCY(A1:A20,A1:A20)<>0,FREQUENCY(A1:A20,A1:A20)))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[YuriHR]

Sandy and Jason, thanks for your help! AhoyNC just posted almost what I need, except for the range that needs to be inside the formula and linked with two cells (from/to).

I've tried a lot of combinations but most of them return #N/A (maybe an signal of a limitation of the array formula, I don't know).

 

[jasonb75]

The proverbial penny has just dropped, I misunderstood what you were asking for, glad you got the answer that you need.

 

[YuriHR]

Thank you for your time, Jason! And I'm sorry, my English is not so good, maybe it was my fault.

But AhoyNC's answer solves part of the problem. I still couldn't find the final formula :/

 

[AhoyNC]

If you turn your column A into a table then the formula will expand if you add dates.
If you want a start and end date then look at the formula in cell E8 below. The formula in E3 is for the whole data range in column A.

Book1
	A	B	C	D	E
1	Column1
2	4/19/2020
3	4/19/2020			Median=	3.5
4	4/19/2020
5	4/19/2020
6	4/19/2020			Start	4/19/2020
7	4/19/2020			End Date	4/22/2020
8	4/19/2020			Median	4.50
9	4/19/2020
10	4/20/2020
11	4/20/2020
12	4/20/2020
13	4/20/2020
14	4/21/2020
15	4/21/2020
16	4/21/2020
17	4/21/2020
18	4/21/2020
19	4/22/2020
20	4/22/2020
21	4/22/2020
22	4/23/2020
23	4/23/2020
24	4/25/2020
25	4/25/2020
Sheet2

Cell Formulas
Range	Formula
E3	E3	{=MEDIAN(IF(FREQUENCY(A2:A25,A2:A25)<>0,FREQUENCY(A2:A25,A2:A25)))}
E8	E8	{=MEDIAN(IF(FREQUENCY(IF($A$2:$A$25>=$E$6,IF($A$2:$A$25<=$E$7,$A$2:$A$25)),$A$2:$A$25)<>0,FREQUENCY(IF($A$2:$A$25>=$E$6,IF($A$2:$A$25<=$E$7,$A$2:$A$25)),$A$2:$A$25)))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.