Combining IF Statement and QUARTILE Statement

[mr.emunson]

I have a column of various percentages and I would like to use the QUARTILE function to get the 50% percentile. The issue I have is that majority of these percentages are zero.

I am looking for a formula to exclude the zeros and compute the 50% quartile of anything non-zero.

My attempt:
=IF(AD8:AD200<>0,QUARTILE(AD8:AD200,2),"")


Any help is very much appreciated, thank you.

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=QUARTILE(IF(AD8:AD200>0,AD8:AD200),2)

 

[mr.emunson]

Worked perfectly!! Thank you very much for your help.

 

[dwhims]

I always love coming to this board for 2 reasons:

1. It warms me to the bones to know I'm not the only one experiencing problems like this; and

2. I know that somewhere out there exists a knowledgeable soul willing and able to help me solve my problems.

This post clearly stated exact issue I was having, and the solution offered worked perfectly for me, too.

Thank you!!!

 

[maribelbc]

Hi,

I'm using the formula below. But if M5 is a zero, i need it to be excluded from the quartile ranking. Possible?

=MATCH($M5,QUARTILE($M$5:$M$17,{4,3,2,1}),-1)

 

[Domenic]

What do you want the formula to return when M5 is a zero? To return #N/A, try...

=IF($M5=0,#N/A,MATCH($M5,QUARTILE($M$5:$M$17,{4,3,2,1}),-1))

 

[maribelbc]

Worked great! just subbed the #n/a for 0 to keep my average YTD. thank you!!!

 

[Vueman]

How does it work when we need the quartile function to be performed based on values in another column? For example column A has cities and column B has population but I need the quartile function to be performed on population but only in certain cities? Thanks

 

[JoeBaker3]

I am working on a similar formula but need to cluster the Quartile statement so I can calculate each value as it relates to the total array. My issues is with the Sum of Sales Hits column it is taking the zero values into account and skewing the product ranking quartile formula.

Formula for Sum of Sales Hits: PRODUCT Ranking

=IF(V2<=0,"D",IF(AND(V2<>0,(V2>QUARTILE($V$2:$V$1990,3))),"A",IF(AND(V2<>0,(V2>=QUARTILE($V$2:$V$1990,2))),"B",IF(AND(V2<>0,(V2>=QUARTILE($V$2:$V$1990,1))),"C",IF(AND(V2<>0,(V2>=QUARTILE($V$2:$V$1990,0))),"D")))))

 

[Muhammad Hussaan]

For example i have column A having values from 1 to 12; multiple 1,2,3 etc (representing months) and column B having production data and i want to calculate the QUARTILE with condition of month 1, month 2, month 3 etc. how to to do?