[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

Calculate percentile of multi row/column data

Posted on 2013-11-12
Medium Priority
451 Views
Hi,

I have attached a file which contains data for which i would like to know where the top 1%, 5% etc of data lies.

The challenge is the data goes across 12 columns but multiple rows. So i have to identify the client type by its number (thats in the rows), then i have to identify the 12 data points by headings (in the columns), although will always be in the same column/row

Then with that block of data, i need to identify where the threshold for the top 1%, 5% etc

Many thanks
Seamus
Seamus-Test-Data.xlsx
0
Question by:Seamus2626
[X]
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

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 39641402
Hello Seamus,

You can get the threshold for the top 1% with an array formula like this

=PERCENTILE(IF(A8:A16=1,B8:M16),99%)

confirmed with CTRL+SHIFT+ENTER

regards, barry
0

Author Closing Comment

ID: 39641440
Perfect, thanks Barry!
0

Featured Post

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …