monthly median based on daily values

[bloodybrit90]

Hey guys,

I would like to find the monthly median price of daily transactions. For an average I can use a pivot table, but it does not have the median function build in. I have calculated the daily median value using an array formula.

=IF(A10>=1,MEDIAN(IF('textexport(1)'!$O:$O=Sheet3!B10,'textexport(1)'!$P:$P)),NA())
column A has the number of sales and column B has all the days. What is the best way for me to get the median of all the data per month?

Cheers

 

[pplstuff]

=median(if(_monthcolumn_=_month_,_valuecolumn_)) <<-entered as an array (ctrl+shift+enter)

You'll need to make a month column if you don't already have one (it can be done without making a month column, but for simplicity make one).

 

[Robert Mika]

Excel Workbook
	A	B	C	D
1	01-Jan	5	January	45
2	02-Jan	84	February	31
3	03-Jan	99	*	*
4	04-Jan	19	*	*
5	05-Jan	73	*	*
6	06-Jan	50	*	*
7	09-Jan	58	*	*
8	10-Jan	42	*	*
9	11-Jan	98	*	*
10	12-Jan	21	*	*
11	13-Jan	4	*	*
12	16-Jan	65	*	*
13	17-Jan	8	*	*
14	18-Jan	69	*	*
15	19-Jan	44	*	*
16	20-Jan	27	*	*
17	23-Jan	99	*	*
18	24-Jan	45	*	*
19	25-Jan	17	*	*
20	26-Jan	35	*	*
21	27-Jan	31	*	*
22	30-Jan	81	*	*
23	31-Jan	92	*	*
24	01-Feb	85	*	*
25	02-Feb	94	*	*
26	03-Feb	45	*	*
27	06-Feb	3	*	*
28	07-Feb	15	*	*
29	08-Feb	26	*	*
30	09-Feb	18	*	*
31	10-Feb	74	*	*
32	13-Feb	82	*	*
33	14-Feb	19	*	*
34	15-Feb	20	*	*
35	16-Feb	4	*	*
36	17-Feb	90	*	*
37	20-Feb	29	*	*
38	21-Feb	31	*	*
39	22-Feb	74	*	*
40	23-Feb	58	*	*
41	24-Feb	82	*	*
42	27-Feb	6	*	*
43	28-Feb	7	*	*
44	29-Feb	65	*	*
Sheet1

 

[bloodybrit90]

I have some #N/A in the values I need the median of. The formula above does returns #N/A because of this. I do not want to count the #N/A

 

[barry houdini]

You can add another IF to filter out non-numeric data, something like

=MEDIAN(IF(MONTH(Daterange)=6,IF(ISNUMBER(Data),Data)))

confirmed with CTRL+SHIFT+ENTER

 

[bloodybrit90]

Not sure what I am doing wrong, but the formula returns #value

=MEDIAN(IF(MONTH($A$2:$A$1124)=MONTH(C2),$B$2:$B$1124)).

 

[barry houdini]

Have you "array entered" the formula so that { and } appear around it?

Entries in $A$2:$A$1124 range and in C2 need to be valid dates or MONTH function returns a #VALUE! error

 

[bloodybrit90]

Column A is formatted as a date (2/14/2012) and column C is formatted as a date (February)....I am using ctr,Shift, enter.

 

[barry houdini]

OK but if you get #VALUE! error I'd still suspect an invalid date somewhere. Try this array formula to give you the row number of the first invalid date

=MATCH(TRUE,ISERROR(MONTH($A$2:$A$1124)),0)+1

confirmed with CTRL+SHIFT+ENTER

If that gives you #N/A then all your dates are OK

 

[bloodybrit90]

when i try =month(column A) I get the correct month (1=january....) when I do the same to column C I get #Value even though it is formatted as a date (format cells=date (M))