Ignore Zero's - Percentile

  • Thread starter Thread starter Chris26
  • Start date Start date
C

Chris26

Hi

I have used the following to get the average value from a set of data whilst
ignoring zero values within the data set.

=SUM(K4:AP4)/COUNTIF(K4:AP4,">0")

I would like to use the percentile command on the same set of data. Is there
a way that I can use Percentile and also ignore zero values ?
Many Thanks
Chris
 
Hi,

with an ARRAY formula

=PERCENTILE(IF(K4:AP4>0,K4:AP4),0.1)

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
You can also do this from VBA by using

rng.FormulaArray = "PERCENTILE(IF(K4:AP4>0,K4:AP4),0.1)"
 
Back
Top