Quantifying improvement in test history

[ERC]

If this has been already been dealt with please point me in the right direction. I am using excel 2010. My question: As a teacher if my student scored a 220 with me, is this a good or bad score for that student? If I could look at the student's past 3 years of test scores (they are all on the same standardized scale) I could see this.

In my spreadsheet rows I have students. In the columns I have their state test score info from 2008-2010. So student 1 for example has a score of 212 in 2008, a 218 in 2009, and a 220 in 2010. I would be able to see that student 1’s trajectory is heading in the right direction.

I created a Sparkline chart to show this visually but I would like a formula to be able to quantify the number who had their highest score in my class or how many did not.

I also want to see if the average (mean) test score for a class of students is going up or down over the years.

At the end of the day, I want to be able to say in my class of 20 students, 12 students had their highest score in my class this year (or whatever). I have a hunch there is some funky lookup formula but I just can’t figure it out.

I am looking fwd to a great solution. Let me know if I need to add anything.
Thanks!

 

[mole999]

is all your data on one sheet ?

 

[ERC]

Yes

 

[mole999]

at the bottom of each column you could use =MEDIAN(F9:F17) which you can then extend across each year, as they learn more the median has to rise

you can rank, and also there are ways of identifying the top 10%

what is the greatest value to you as a teacher or them as students

 

[ERC]

The issue as a teacher is that you tend to have 100-150 students. You think you might be a doing a good job. If you know that most of your students have their best scores with you, then you are really adding value. If you are in a low income urban setting, your scores may not be that high, but you might be doing a great job. Or you may be fooling yourself and thinking that you are when you are not.
I really am looking for a mechanism/formula to quantify exactly how many students have their best year, how many do not, and how many tie.
I will try the median function you suggested but I don't think that really is what I am going for.
Thanks

 

[ERC]

Re: Help with formula

I know what I want excel to do but don't know how to tell it to do it through a formula. If I have the follow dataset

2008 2009 2010
220 230 221

I want to find out is the score in column 3 row 2 greater than the score in column 1 row 2? Is it also greater than the score in column 2 row 2? If it is, count it as "greater". If it not count it as "Lower". Then count up all of the scores that are greater than and all that are less than (pretending the data set includes many rows). For example, there are 20 rows of scores representing my class. In this class, 15 had "greater scores" and 5 had "lower".
Thanks

 

[Charlie45]

In D2
=IF(AND(C2>A2,C2>B2),"Greater","Lower")

 

[Charlie45]

To count Greater =COUNTIF(D2:D8,"Greater")

To count Lower =COUNTIF(D2:D8,"Lower")

Change the D2:D8 to match your range

 

[shg]

I also want to see if the average (mean) test score for a class of students is going up or down over the years.

If A1:A10 contain test scores over some time interval, then =LINEST(A1:A10) tells you the slope of those scores versus the categorical series {1,2,3,...,10}. Positive is good.

 

[ERC]

Thanks it works!