Solved

Rank percentile from list of data

Posted on 2013-11-08
2
258 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 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 250 total points
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now