Top 10% of Sorted Data

[andy3919]

Good Day All.

I have a data set of about 600 records. I want to be able to extract the Top 10% of the records and get rid of the other 90% - the values can change over time, so I always want to start with the full 600 records.

I am aware of the conditional formatting function that will identify the Top 10%, but I was looking for VBA language (does not have to be VBA - just automated), that calculated the 10% subset and either moved it to a different sheet, OR deleted the bottom 90% - in either instance the result being just the list of the top 10% of the data set.

I hope that makes sense.

Thanks in advance!!!

 

[Gerald Higgins]

How do you define the top 10% ?

For example, if you have exactly 600 records, is it always the top 60 records ?
Or is it those records which account for the top 10% of some other statistic ? Which will almost certainly be less than 60, and a varying number from time to time.

 

[andy3919]

thank you for the reply. The list is 600 stocks. I will always want to see the 60 (10%) that have the highest volume. (Having said that, I would like to make the 10% search criteria a variable, so if I wanted to, I could extract the top 15%, etc.). But, it will always be based on the "Volume" criteria.

thank you.

 

[GaryOfah]

In column A type volume in rows A1:A10
In cell B1 type formula: =A1/((MAX($A$1:$A$10))/100)
Pull formula from B1 to B10
Now you have best by percentage.

Then you can record macro to sort it out in other column.
Add column with names. Sort column “volume" and column “names” together in macro.
From top to bottom and now you have all sorted by volume.
Then you have all records by %. First 60 are best if they are up to 100% max.

 

[GaryOfah]

Or maybe something like this...
Rank Formula in Excel

 

[GaryOfah]

Or as I said with RANK:
=RANK(B1/((MAX($B$1:$B$10))/100),$C$1:$C$10,0)

Then someone have to implement CHOOSE formula I believe, to retrieve name from rank number, by order, automatically.
Or something similar. That way no need for macro, buttons etc.

 

[GaryOfah]

I have better solution. Just wait few more minutes for me to implement and simplify...

PS. Anyone, do not dare to solve this pain before me!!
Do other stuff and challenges...

 

[andy3919]

I have better solution. Just wait few more minutes for me to implement and simplify...

PS. Anyone, do not dare to solve this pain before me!!
Do other stuff and challenges...

Thanks Gary...waiting!

 

[GaryOfah]

Huh... Gary!!
Proudly presents!
Program vo..me No1.

1.Check everything thrice or many times more.

2.I have spent quite time with this riddle.

3.”Program” can use up to 5 same volumes. You can add some additions into formulas if there can be more same volumes. But even this 5 is for precaution and i think - very unlikely to happen. You can use even same stock names, under some conditions! As if on SE can be any?! But never say never.
Else it will shoot error #N/D, under some conditions when volume is the same and when numbers fall under search n% condition!
Same volumes are just one after another collected with their names. If there are more than one stock with same volume.

4.Use whole numbers when setting search for e.g. best: 10 %. E.g. 1,2,3,4,50,70...

5.Stock names and volumes doesn’t have to be in row after row, just make sure that the range you are searching is correct. Ok I will give you 1000 rows for that purpose.

Final but not the least important - If you or anyone else can help me with my problems, then thank You very much! Try.

Cell A1 “name” A2 to A1001 type/get names

Cell B1 “volume” B2 to B1001 type/get volume

Cell C1 “rank” C2 to C1001 type/copy/paste =IF(COUNTIF($B$2:B2,B2)=2,RANK(B2,$B$2:$B$1001,0)+1,IF(COUNTIF($B$2:B2,B2)=3,RANK(B2,$B$2:$B$1001,0)+2,IF(COUNTIF($B$2:B2,B2)=4,RANK(B2,$B$2:$B$1001,0)+3,IF(COUNTIF($B$2:B2,B2)=5,RANK(B2,$B$2:$B$1001,0)+4,RANK(B2,$B$2:$B$1001,0)))))

Cell D1 “rank number” D2 to D1001 D2 type/copy/paste 1, D3 =D2+1...

Cel E1 “name by rank” E2 to E1001 type/copy/paste =IF(AND(D2 > 0,D2 < =$G$1),OFFSET($A$1,MATCH(D2,$C$2:$C$1001,0),0),"")

Cell G1 type =(600/100)*H1

Cell H1 type 1 or 2,3,4,5...for percent

Enjoy!

 

[GaryOfah]

And yes, you can add formulas to count for all names and then count percentage of that value, if you don't have always 600 names but you want exact percentage of the actual number of names.

Huh, no more complicating for now!