[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 455
  • Last Modified:

Calculate percentile of multi row/column data

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
Seamus2626
Asked:
Seamus2626
1 Solution
 
barry houdiniCommented:
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
 
Seamus2626Author Commented:
Perfect, thanks Barry!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now