median in excel, ignoring an #N/A

pipiet

New Member
Joined
Sep 27, 2014
Messages
4
Hi,
I have 10 series of data and I want to find median of each series using excel, but my data contains some #N/A.
I try using this formula: =MEDIAN(IF(ISNUMBER(E22:E32),E22:E32))
but the result is all zero.
can you help me? Thanks
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,309
Office Version
  1. 2010
Platform
  1. Windows
Hi,
I have 10 series of data and I want to find median of each series using excel, but my data contains some #N/A.
I try using this formula: =MEDIAN(IF(ISNUMBER(E22:E32),E22:E32))
but the result is all zero.
can you help me? Thanks
Try committing the formula using CTRL+SHIFT+ENTER and not just Enter by itself.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
If you are using Excel 2010 or later you could also use AGGREGATE function to get the median and ignore errors, e.g. with this non array formula:

=AGGREGATE(12,6,E22:E32)

12 indicates MEDIAN and 6 means ignore errors
 

pipiet

New Member
Joined
Sep 27, 2014
Messages
4
Try committing the formula using CTRL+SHIFT+ENTER and not just Enter by itself.

I have another problem. I want to run some simple regression using excel, but my data contains some NAs in it.
I try to find the constant (c), the slope (b), the residuals, for the following examples:
Y2005 = c + bX2005;
Y2006 = c + bX2006;
Y2007 = c + bX2007;
Y2008 = c + bX2008;
ect
Here is the data screenshot:

-- removed inline image ---


Can you help me again please.
Thank you so much in advance.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,987
Messages
5,526,069
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top