median in excel, ignoring an #N/A

[pipiet]

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

 

[Rick Rothstein]

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.

 

[pipiet]

Try committing the formula using CTRL+SHIFT+ENTER and not just Enter by itself.

Thank you... it works.

 

[barry houdini]

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]

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.