Quartile\Median FUnction Excluding the 0 Value Excel 2007

[johnzippons]

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

 

[RatExcel]

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

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]

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]

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]

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