Chart the Top 3 values

[Ryan]

I'm a programmer, but I don't program in Excel normally so
bear with me.

I currently have an Excel chart that displays the
frequency of Accidents in Man Hours Lost for the overall
Program with the x-axis being each Qtr. Within this
Program are 40 Projects. What I would like to do is chart
the top 3 most accident prone Projects as an overlay on
the existing chart. I've worked out how to add a combo box
that will add a series to the chart based on the user's
selection(s), which gives them the ability to add desired
project ranges to the chart. But, I would like to make
this an automatic thing.

I've looked and have only found things like getting the
Min/Max values for a range. What I'd like to do is sort
the frequency range in descending order and then just run
a loop for the first 3 to get what I want, but I can't
find the syntax to make it work.

Example of data to be charted:

Accident Frequency by Quarter (calculated from previous
two sheets, Manhours/month & Accidents/month)
QTR4 FY01 QTR2 QTR3
2A1 1.77 1.77 1.94 3.38
IM&T 0.00 0.00 0.00 0.00
Ped 2.25 2.54 0.00
Elev 25.20 18.55 15.22 17.87

What I want to do is just chart the top 3 of those by
their total frequency over the period of time (Qtrs). The
current chart is linked, dynamically, to the Frequency
sheet so I can't just go in the sheet and Sort because it
would screw up the existing chart.

Any ideas on how best to accomplish this would be much
appreciated.

Thanks,
Ryan

 

[Tushar Mehta]

If your raw data (not the calculated and tabulated data set you shared
in your post) are organized in a table that meets the prerequisites for
use in a relational database, you could create a PivotChart and use its
'Top 10' feature. Yes, the Top 10 is configurable to Top 'n'.

Alternatively, if you want to overlay the data on the same chart, just
color those series that are in the top 3 in a different color, say,
red. To automate the process, duplicate the top 3 series in a separate
column (with the appropriate IF statements) and add that series to the
existing chart.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions