Chart series (based on datarange content)

[alecswan]

I copied this thread from Miscellaneous forum, but I don't know how t
delete the original threa
(http://www.excelforum.com//showthread.php?t=563052).

I am trying to create a chart, which will show how execution times o
certain methods change over time. Here is my data, the first colum
lists method names and the second execution times in milliseconds:

find(), 100
create(), 200
find(), 75
find(), 125
create(), 250

I would like to create a chart, which has a separate series for eac
distinct method name and will show how method execution time chage
over time. So, in the case above, I would have two series: one - fo
find() method, another - for create() method. Each series will plo
execution time for the pertinent method on the Y-axis and us
sequential number for X-axis. So, for the example above, the firs
series will plot (1, 100), (2, 75), (3, 125) and the second series wil
plot (1, 200), (2, 250). I am not concerned about X-values as long a
they are sequential.

One caveat though, I would like to be able to run this chart agains
different data ranges, which can contain different sets of method
being executed. So, I don't want to MANUALLY create a separate serie
for each method, but instead would like the chart to figure out ho
many different method names are there and create a separate series fo
it.

Thanks.

Ale

 

[alecswan]

Can anybody help with this problem?

 

[Tushar Mehta]

Add another column that gives you a sequential count of each method, and
then create a PivotTable with a corresponding PivotChart.

Suppose your data are in A:B. If you don't already have a header row,
create one. I used the column headers: Method, Speed, and Count for column
C, my new column.

In C2, enter the formula =COUNTIF($A$2:A2,A2)
Copy C2 as far down col. C as you have data in A:B. The easiest way to do
this is move the mouse to the right-bottom corner of C2 where it becomes an
equal-sized cross. Now, double-click.

Create a PT (Data | PivotTable and PivotChart Report...). Use the Method as
the column field, the Count as the row field, and the Speed as the data
field.

Click anywhere inside this table, then click the Chart Wizard button. XL
will create a chart in its own sheet. Select Chart | Chart Type... | and
switch to a Line chart.

--
Regards,

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