How to Select Successive Groups of Individuals for Trendlines

[Chick]

Hi,

I'm new to VBA programming and am stuck on how to select successive groups
of cases. I've got a total of 300 people with test scores taken during 3
time periods (coded 0, 1, 2). To illustrate how the coding looks, here's how
the data appears for just the first three people:

A B C
1 id time test
2 1 0 51.66
3 1 1 52.09
4 1 2 56.72
5 2 0 49.92
6 2 1 60.26
7 2 2 66.33
8 3 0 51.12
9 3 1 52.63
10 3 2 57.74


The idea is to start with the first person (id = 1) and use the time series
(0, 1, 2), and corresponding test scores (51.66, 52.09, 56.72) to create a
chart that plots the 3 scores and adds a trendline. Then move on the the
second person, third person, etc. and use their corresponding time series and
scores to add two more trendlines to the chart. The result would show
trendlines for each of the 300 people on the same chart.

I thought of using IF-THEN or SELECT statements but it seems I'll need to
list all 300 ids, time series, and test scores but it doesn't seem to be an
efficient way of doing things.

Any ideas would be appreciated. Thanks in the advance.

 

[Joel]

First Excel is limited to 250 series on a single chart. You have 300. I
woulld also re-roganize your data as follows

1 id time 0 time 1 time 2
2 1 51.66 52.09 56.72
3 2 49.92 60.26 60.33
4 3 51.12 52.63 57.74

 

[Chick]

Thanks for the tip - I see now how the data is better organized. I'm now
thinking if putting in a counter (using id) or loop would help in processing?

Chick

 

[Joel]

You don't have to do anything. Select the header columns Time 0 thru time 2
and the columns below containing the number. dont highlight the ID number or
row numbers. then go to worksheet menu Insert - Chart - Line - finish. Each
row of three numbers will automaticaly become its own series.

 

[Chick]

Brilliant! Besides not coding the data correctly I was erroneously using
scatterplot and adding a trendline via that method. Thanks very much for the
help!

Chick