Percentile formula not working

[Ratan Bhushan]

Hi Team
I want in column H the desired result which already there by a formula, but there is a glitch, its showing for all calls data.
I want my formula to pick only those contacts which are more than 100. if there is no 100 then there must be BLANK but its showing
Q5. I am unable to understand where I am wrong.
Please help.
Thanks and Regards
RatanB


PS I have attached the file.

 

[Faseeh]

Try inserting IF() within percentile function so that values feed to percentile are equal to 100. and try using a vlookup table or lookup() to fetch the values.

 

[NARAYANK991]

Hi Ratan ,

Can you not use the following formula in H2 ?

=IF(E2>100,G2,"")

Enter this in H2 , and copy down.

Narayan

 

[Ratan Bhushan]

Hi Ratan ,

Can you not use the following formula in H2 ?

=IF(E2>100,G2,"")

Enter this in H2 , and copy down.

Narayan

SIr I can not use this because percentile function will be calculation the array only of those values which are more than 100. otherwise I ll be getting the same value which is in G2.

Hence I need to consider only more than 100 contacts, and out of them I need to know the value between Q1,Q2,Q3,Q4,Q5.

Please help sir...

 

[Vivek D]

Probably simplest option would be to introduce a helper column which only shows the CPS% if contacts is >100 and then use that in the Quintile > 100 formula with a condition that shows blank if contacts is not > 100. See attached.