# Rank percentile from list of data

Hi,

I have a column of data, some rows are 0, some are not.

I want some formula that will sum the data and return the data amount that falls in the top 1% of data, what formula could achieve this?

Many thanks
###### Who is Participating?

x

Commented:
Hello Seamus2626,

Perhaps Rgonzo's suggestion will work for you but I think it only gives you the cutoff point. If you want to sum everything above that point as you say then you need to use the percentile within a SUMIF fomula, e.g. if data is in A2:A1000 try

=SUMIF(A2:A1000,">="&PERCENTILE(IF(A2:A1000>0,A2:A1000),0.99))

confirmed with CTRL+SHIFT+ENTER

That assumes you want to exclude zeroes from the calculation.....but you didn't say that, so if zeroes should be included it's just

=SUMIF(A2:A1000,">="&PERCENTILE(A2:A1000,0.99))

which can be entered normally

regards, barry
0

Commented:
Hi

you could enter an Array formula like this

``````=PERCENTILE.INC(IF(A2:A5<>0,A2:A5),0.99)
``````
confirmed with CTRL+SHIFT+ENTER

Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.