# median in excel, ignoring an #N/A

#### pipiet

##### New Member
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

### Excel Facts

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

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

Thank you... it works.

#### barry houdini

##### MrExcel MVP
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
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:

Replies
0
Views
22
Replies
2
Views
36
Replies
1
Views
26
Replies
1
Views
35
Replies
1
Views
74