Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract top 5 elements from table
Hi,
I know it can be done, but I can't get the logic to work. In excel 2003, I have a table of 20 service names and the corresponding number of issues in each service for the past 6 rolling months. The names are static, but the Service / -5mo / -4mo / -3mo / -2mo / -1mo / this mo / Total last 6 months S1 /120 / 110 / 100 / 90 / 84 / 16 / 520 S2 S3 .... S20 I use this table to report on the issue counts for all services. I also need to prepare a panel chart based on the top 5 services with the most issues. In any month the services that make it to the chart can change based on their 6 month total. So I need to find a way to extract using formulas, the service name and the corresponding data values into a feeder range that my panel chart can use. The output does not need to be ordered. My output (feeder range) would look like S1 xx xx xx xx xx xx 520 S5 xx xx xx xx xx xx 559 S9 S10 S18 I have tried using rank() on the totals and then filtering but couldn't get the panel chart to work cleanly. Thanks to Jon Peltier's blog & website. Is this a job that match(), index(), offset() and largest() can handle without VBA? Or do I need to resort to a pivot table? Any ideas? Thanks, Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract top 5 elements from table
On Aug 2, 10:10 am, Bob wrote:
Hi, I know it can be done, but I can't get the logic to work. In excel 2003, I have a table of 20 service names and the corresponding number of issues in each service for the past 6 rolling months. The names are static, but the data elments are fed from a database using index() etc. Service / -5mo / -4mo / -3mo / -2mo / -1mo / this mo / Total last 6 months S1 /120 / 110 / 100 / 90 / 84 / 16 / 520 S2 S3 ... S20 I use this table to report on the issue counts for all services. I also need to prepare a panel chart based on the top 5 services with the most issues. In any month the services that make it to the chart can change based on their 6 month total. So I need to find a way to extract using formulas, the service name and the corresponding data values into a feeder range that my panel chart can use. The output does not need to be ordered. My output (feeder range) would look like S1 xx xx xx xx xx xx 520 S5 xx xx xx xx xx xx 559 S9 S10 S18 I have tried using rank() on the totals and then filtering but couldn't get the panel chart to work cleanly. Thanks to Jon Peltier's blog & website. Is this a job that match(), index(), offset() and largest() can handle without VBA? Or do I need to resort to a pivot table? Any ideas? Thanks, Bob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract top 5 elements from table
On Sat, 2 Aug 2008 07:13:14 -0700 (PDT), Bob
wrote: On Aug 2, 10:10 am, Bob wrote: Hi, I know it can be done, but I can't get the logic to work. In excel 2003, I have a table of 20 service names and the corresponding number of issues in each service for the past 6 rolling months. The names are static, but the data elments are fed from a database using index() etc. Service / -5mo / -4mo / -3mo / -2mo / -1mo / this mo / Total last 6 months S1 /120 / 110 / 100 / 90 / 84 / 16 / 520 S2 S3 ... S20 I use this table to report on the issue counts for all services. I also need to prepare a panel chart based on the top 5 services with the most issues. In any month the services that make it to the chart can change based on their 6 month total. So I need to find a way to extract using formulas, the service name and the corresponding data values into a feeder range that my panel chart can use. The output does not need to be ordered. My output (feeder range) would look like S1 xx xx xx xx xx xx 520 S5 xx xx xx xx xx xx 559 S9 S10 S18 I have tried using rank() on the totals and then filtering but couldn't get the panel chart to work cleanly. Thanks to Jon Peltier's blog & website. Is this a job that match(), index(), offset() and largest() can handle without VBA? Or do I need to resort to a pivot table? Any ideas? Thanks, Bob Assuming you input data is in columns A to H on rows 2 to 21 and that you want your resulting table ot be in columns K to R starting on row 2, put the following formula in cell R2 Note: This is an array formula that has to be entered by CTRL+SHIFT+ENTER rather that just ENTER. =INDEX(A$2:A$21,MATCH(LARGE($H$2:$H$21,ROW()-1),$H$2:$H$21,0)) Copy to the right to column R and then down to row 6 Hope this helps / Lars-Åke |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract top 5 elements from table
Please put some info in the MESSAGE box next time.
You may want to use the function LARGE. -- HTH, Barb Reinhardt "unknown" wrote: |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract top 5 elements from table
On Aug 2, 12:03 pm, Barb Reinhardt
wrote: Please put some info in the MESSAGE box next time. You may want to use the function LARGE. -- HTH, Barb Reinhardt "unknown" wrote: Hi The array function worked great! Thank you very much. Cheers, Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Table Lookup using two data elements | Excel Worksheet Functions | |||
How do I randomly select data elements from a table? | Excel Discussion (Misc queries) | |||
Restricting Data Elements in Pivot Table | Excel Worksheet Functions | |||
Make a list from a table of elements ? | Excel Programming | |||
Pivot table with form elements | Excel Programming |