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

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

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

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:

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