Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Rank percentile from list of data

Posted on 2013-11-08
2
265 Views
Last Modified: 2013-11-11
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
0
Comment
Question by:Seamus2626
2 Comments
 
LVL 50

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
ID: 39634282
Hi

you could enter an Array formula like this

=PERCENTILE.INC(IF(A2:A5<>0,A2:A5),0.99)

Open in new window

confirmed with CTRL+SHIFT+ENTER

Regards
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 250 total points
ID: 39634652
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Distribute Values over date range 15 36
first name and last initial in excel 11 28
Highlight changing numbers in column 3 18
Excel VBA 30 38
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question