How do I calculate age standardised scores.

[banana]

I am putting some data onto windows excel and I would like to include age standardised scores . I am reasonably capable with numbers and following instructions but please make it as simple as you can,Ive had a hard day !

[Hippy]

Now I think I grasp the question. As I understand there are tables of weighted scores for test results that can accommodate an age range and hence ability range in a school year group. So, for example, a 10yr old scoring 12/20 is deemed to have the same attainment as a 10yr and 7month old kid with a score of 10/20. There are some Excel tables at
that target=_blank>http://www.qca.org.uk/ca/tests/teacherhelp/age_sco
res.asp#ks2
that hold some data of this sort.

Without going into arcane elaboration, I modified one of these tables, and have posted it here
I target=_blank>http://www.timmonet.co.uk/scores.xls
I used a combination of the INDEX and VLOOKUP functions an the addition of a further vertical lookup table to find the age index and hence the column to perform the INDEX function on.

It may not be exactly what you are looking for, but you will see the little coloured area I created where you can enter a score and then an age (year.months) to return a weighted score.

You can nest this formula within other functions to work on some of your real data. For example, your scores might be out of 50 rather than out of 20 as in the table. Just divide by 2.5 and round to the nearest integer to get the right table lookup value. something like =INT(P35/2.5) should do the trick, where P35 contains the score in question.

A little thought should reveal a way of discovering my email address if you wish to converse further about this.

[Hippy]

Here's another try. The first link is toQCA.org and goes like this http://www.qca.org.uk/ca/tests/teacherhelp/age_sco
res.asp#ks2 and the second link is to the amended version of one table with my formlae included at http://www.timmonet.co.uk/scores.xls