How to get the top X% of values from a list with many different variables

[nlarski]

I need to return the top 90% of values for hundreds of different variables. I thought this would be an easy task but when I started on it I realized that I had no idea how to approach it.

Here is how everything is set up..I have 1300 unique ID's in one column, all with anywhere from 10 to 100 numeric values associated with them. For each ID, I need to extract the top 90% of values and get a count per ID.

Any help would be appreciated! Thanks!

 

[JoeMo]

Can you post a sample of your data in a form that can be copied and pasted into an Excel worksheet?

 

[cebalaw2]

Without looking at your data, I would guess you should be able to use the Excel function PERCENTILE to calculate the 90th percentile.
If column A is the ID, and associated values are in columns 2 through 101, then:
A102: =PERCENTILE(A2:A101,0.9)
If you need a count:
A102: =COUNTIF(A2:A101,">="&PERCENTILE(A2:A101,0.9))

 

[nlarski]

Yeah, that would probably help! So this is how it is set up but obviously on a much larger scale https://imgur.com/a/ERFgZ

 

[cebalaw2]

[TABLE="width: 504"]
<colgroup><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Data[/TD]
[TD]Address First Cell[/TD]
[TD]Address Last Cell[/TD]
[TD]90th Percentile[/TD]
[TD]Count >= 90th Percentile[/TD]
[/TR]
[TR]
[TD]ID1[/TD]
[TD]56[/TD]
[TD]$B$2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ID1[/TD]
[TD]39[/TD]
[TD]$B$2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ID1[/TD]
[TD]76[/TD]
[TD]$B$2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ID1[/TD]
[TD]81[/TD]
[TD]$B$2[/TD]
[TD]$B$5[/TD]
[TD]79.5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ID2[/TD]
[TD]24[/TD]
[TD]$B$6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ID2[/TD]
[TD]29[/TD]
[TD]$B$6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ID2[/TD]
[TD]19[/TD]
[TD]$B$6[/TD]
[TD]$B$8[/TD]
[TD]28[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ID3[/TD]
[TD]30[/TD]
[TD]$B$9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ID3[/TD]
[TD]31[/TD]
[TD]$B$9[/TD]
[TD]$B$10[/TD]
[TD]30.9[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Just an example of how you may set it up (without needing macros); ID1 begins in cell A2 in this example and the columns are adjacent.
Address First Cell (B2): =IF(A2=A1,C1,ADDRESS(ROW(),2))Address Last Cell: =IF(C2=C3,"",ADDRESS(ROW(),2))
90th Percentile: =IF(D2<>"",PERCENTILE(INDIRECT(C2&":"&D2),0.9),"")
Count >= 90th Percentile: =IF(E2<>"",COUNTIF(INDIRECT(C2&":"&D2),">="&PERCENTILE(INDIRECT(C2&":"&D2),0.9)),"")
Then just drag down through your data.