# Quartile\Median FUnction Excluding the 0 Value Excel 2007

#### johnzippons

##### New Member
Good afternoon.

I have this certain Formula on Excel 2007

=QUARTILE('Input for ongoing tracking'!BD\$7:BD\$39;1)/G25

This is to measure Low Quarter \ Median \ Top Quarter, and this formula above its to the Low Quarter
It is lowering my results because of the zeros contained on the Range of Collum BD between line 7 and 39.

What can i add to this formula to get the 0 of that range excluded?

Thanks in advance

João NUnes

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### RatExcel

##### Board Regular
Code:
``=AGGREGATE(17,6,IF('Input for ongoing tracking'!BD\$7:BD\$39<>0,'Input for ongoing tracking'!BD\$7:BD\$39,NA()),1)/G25``

When entering the formula use CTRL+SHIFT+ENTER instead of ENTER only

#### johnzippons

##### New Member
Thanks Mr.Rat but it doesn´t work just copying the formula and use CTRL+SHIFT+ENTER

from where came 17 and 6 and NA ?!

#### RatExcel

##### Board Regular
Which version of Excel are you using? NA() is in IF function so that if any value in the given array is equal to zero it will be converted into #NA error, 17 and 6 are AGGREGATE function's arguments - 17 is for QUARTILE.INC function, 6 to ingore the errors - the ones that we generated using NA() function. Could you please post your workbook on box.com for example?

#### johnzippons

##### New Member
My Excel version is 2007. IF the version where the 2010 i would be able to apply the Quartile.Exc but its the 2007.

I am going to simplify:

I have a collumn with 40 values ( between 0 and 4 ), what i want is to calc the quartile of those 40 values without the 0´s but only between 1 to 4 .

#### barry houdini

##### MrExcel MVP
Hello João, welcome to MrExcel

Try using an "array formula" like this

=QUARTILE(IF('Input for ongoing tracking'!BD\$7:BD\$39>0;'Input for ongoing tracking'!BD\$7:BD\$39);1)/G25

confirmed with CTRL+SHIFT+ENTER

Replies
6
Views
977
Replies
3
Views
329
Replies
1
Views
2K
Replies
8
Views
562
Replies
2
Views
292

Threads
1,191,069
Messages
5,984,451
Members
439,888
Latest member
c3rberus

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

### Disable AdBlock

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