Returning Bottom 10 Percent in another spreadsheet

[Brandon]

I am looking for a way to take a list of data and transfer this to another
spreadsheet, identifying who the low performers for a metric might be. The
data is laid out as below.

A B C
Name Value Rank

There are hundreds of rows of data. I am wanting to return on a seperate
spreadsheet the only individuals who rank in the bottom 10% and their
corresponding information. I would like to generate a formula that will
allow this to autopopulate based on the amount of data each time the report
is modified.

 

[Shane Devenshire]

Hi,

With the data selected choose Data, Filter, AutoFilter, open the dropdown in
the Rank column and choose Top 10, from the first drop down pick Bottom and
from the 3rd dropdown choose Percent.

Highlight the results and Copy. Move to a new sheet and paste.

 

[Brandon]

I'm trying to create this in a format that it will auto populate the sheet
and only pull across the Bottom 10%. There are other colums as well that
have other items ranked and I am not able to use the filter approach as it
will have me replicate the data across multiple sheets.

I am trying to transfer only the bottom 10% across to another worksheet.

I appreciate the advice.

 

[Max]

Here's one way to model it up dynamically ..

Assume source data (Name-Value-Rank) is in sheet: x, cols A to C,
data from row 2 down to row 20

In D2: =PERCENTRANK($B$2:$B$20,B2)
Copy D2 down to D20. For better granulation, I'd point this to the source
col B (Values) which col I presume you are using to rank in your col C (Rank)
using RANK?

Then in another sheet,
In A2: =IF(x!D2<=10%,ROW(),"")
Leave A1 empty. This is the bottom 10% criteria implemented, easy to
understand/adapt if desired.

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(x!A:A,SMALL($A:$A,ROWS($1:1))))
Copy B2 to C2. Select A2:C2, copy down to C20. Minimize col A. Cols B to C
will dynamically return the required bottom 10% of lines from x, all neatly
packed at the top.

Adapt to suit the extents of your actuals.
Press the YES button below to high-five this response, if it helped.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik

 

[Max]

Copy B2 to C2 ...
Just extend the copying across of B2 further to D2, if you want to bring the
source "Rank" col as well. Or to E2 even, if you'd like the percentrank col
as well.

Press the YES button below to high-five this response, if it helped.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---