compare student data over time
 

I have been working on this problem for a couple weeks with some goo
results, but I need some help with the next step.
An annual test that shows student progress is given once a year. W
have scores for hundreds of students from 2001-04. I have set up
table that shows the data as:
Student Test 2001 Test2002 test 2003 test2004
I made it so that the letter scores (B=beginner, EI=Early Intermediate
I=intermediate, etc.) were automatically given a number next to eac
test column (there are 5 levels). Then, another column after that t
display if the the student was on track with developing a level pe
year by using an IF/THEN statement with conditional formatting t
dispay a color. Green=on track, Yellow=stayed at same level, Red=fell
level

So, we can have:
student Test1 Score1 Test2 Score2 Result2 Test3 Score3
Result3
jdoe B 1 EI 2 Green
EI 2 Yellow

In the example, the student above was on track with test 2, going u
one level, but then stayed at that same level on the 3rd score an
recieved Yellow. There is no color result after Score1 because that i
the base score and no color is necessary there. This is working OK.
Here is the statement for Result2:
=IF(G2="","",IF(G2D2,"Green",IF(G2=D2,"Yellow",IF (G2<D2,"Red"))))

Here is the statement for Result3:
=IF(J2="","",IF(J2D2+1,"Green", IF(J2=D2+1,"Yellow"
IF(J2<D2+1,"Red"))))
They looks for growth from test 1.

Works OK, problem is... what about students that don't start on th
first test in 2001? I need to know how to create something tha
compares the scores over time and starts with the students first score
whatever test year that might be.
Do I need to start using VBA to do something like this

--
dserrat
-----------------------------------------------------------------------
dserrato's Profile: http://www.excelforum.com/member.php...fo&userid=1649
View this thread: http://www.excelforum.com/showthread.php?threadid=27876