# monthly median based on daily values

#### bloodybrit90

##### Board Regular
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

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
=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).

Excel Workbook
ABCD
101-Jan5January45
202-Jan84February31
303-Jan99**
404-Jan19**
505-Jan73**
606-Jan50**
709-Jan58**
810-Jan42**
911-Jan98**
1012-Jan21**
1113-Jan4**
1216-Jan65**
1317-Jan8**
1418-Jan69**
1519-Jan44**
1620-Jan27**
1723-Jan99**
1824-Jan45**
1925-Jan17**
2026-Jan35**
2127-Jan31**
2230-Jan81**
2331-Jan92**
2401-Feb85**
2502-Feb94**
2603-Feb45**
2706-Feb3**
2807-Feb15**
2908-Feb26**
3009-Feb18**
3110-Feb74**
3213-Feb82**
3314-Feb19**
3415-Feb20**
3516-Feb4**
3617-Feb90**
3720-Feb29**
3821-Feb31**
3922-Feb74**
4023-Feb58**
4124-Feb82**
4227-Feb6**
4328-Feb7**
4429-Feb65**
Sheet1

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

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

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)).

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

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.

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

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))

Replies
3
Views
176
Replies
7
Views
286
Replies
7
Views
216
Replies
14
Views
485
Replies
2
Views
291

1,207,280
Messages
6,077,508
Members
446,287
Latest member
tjverdugo85

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back