CODEWITHSUNDEEP
excel
user2684423
user2684423

Reputation:

Ignoring zeros in columns

I want to ignore zeros in a specific column using Percentile function. I was seeking out for the Aggregate function , but it does not work. I have also used the following formula, but it did not work: 

 =PERCENTILE(IF(D1:D8,0.3),0.3"<>0")

Here are data : I do not want to use it as an array

30
24
23
12
0
17
15
14

Upvotes: 1

Views: 223

Answers (2)

Scott Craner
Scott Craner

Reputation: 152495

The correct Aggregate Function:

=AGGREGATE(16,6,$A$1:$A$8/($A$1:$A$8<>0),0.3)

enter image description here

Upvotes: 3

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

If you don't want to use an array formula then you can use a helper column. In E1 enter:

=IF(D1=0,"",D1)

and copy down. Then in another cell enter:

=PERCENTILE(E1:E8,0.3)

enter image description here

Upvotes: 2

Related Questions