Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning Bottom 10 Percent in another spreadsheet
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning Bottom 10 Percent in another spreadsheet
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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Brandon" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning Bottom 10 Percent in another spreadsheet
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. "Shane Devenshire" wrote: 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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Brandon" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning Bottom 10 Percent in another spreadsheet
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 --- "Brandon" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning Bottom 10 Percent in another spreadsheet
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning Bottom 10 Percent in another spreadsheet | Excel Worksheet Functions | |||
Percent of a Spreadsheet Complete | Excel Worksheet Functions | |||
Conditional Formatting Bottom 'n' percent | Excel Discussion (Misc queries) | |||
How can I change all values in a spreadsheet by a certain percent | Excel Discussion (Misc queries) | |||
Increasing a whole Spreadsheet by a percent | Excel Discussion (Misc queries) |