# 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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,017
Messages
5,834,962
Members
430,330
Latest member
drAli77

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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