HELP needed with an average formula that takes into account numbers and text

Try this array formula entered with CTRL + SHIFT + ENTER

=INDEX($N$4:$N$15,ROUND(AVERAGE(MATCH(G4:I4,$N$4:$N$15,0)),0))

N4:N15 is a list of the grades in Ascending order.

Originally Posted by Jonmo1

Try this array formula entered with CTRL + SHIFT + ENTER

=INDEX($N$4:$N$15,ROUND(AVERAGE(MATCH(G4:I4,$N$4:$N$15,0)),0))

N4:N15 is a list of the grades in Ascending order.

Hi Jonmo1,

Thank you very much for the formula, it seems to have worked an absolute treat and saved me from tearing my hair out. Another question that you may know the answer too is:

In my 'baseline level' column the students will be given a level when they come into school which again will be a 3a,3b, 3c and so on and each student should make 2 sub levels of progress each year, so for example on my excel sheet student 1 has a baseline assessment of a 3a that students 'end of year target level' is then a 4b and their 'end of KS3 target level' is a 5a. is there a way/formula that when I put the students 'baseline level' in it automatically calculates both their 'End of year level' and their 'End of KS3 target level'.

I know your probably thinking its just as easy to manually input the values but I am dealing with in excess of 800 pupils so therefore this will save hours.

Thanks in advance for your help

Can anyone help )(PLEASE SEE MY ATTACHMENT IN THE PREVIOUS POST)

In my 'baseline level' column the students will be given a level when they come into school which again will be a 3a,3b, 3c and so on and each student should make 2 sub levels of progress each year, so for example on my excel sheet student 1 has a baseline assessment of a 3a that students 'end of year target level' is then a 4b and their 'end of KS3 target level' is a 5a. is there a way/formula that when I put the students 'baseline level' in it automatically calculates both their 'End of year level' and their 'End of KS3 target level'.

I know your probably thinking its just as easy to manually input the values but I am dealing with in excess of 800 pupils so therefore this will save hours.

Thanks in advance for your help

Originally Posted by XOR LX

You need to explain your logic a bit more. You say "student should make 2 sub levels of progress each year", so I can see that a student who has a Baseline Level of 3a would expect to have an End of Year Target Level of 4b, but I don't see how you get the value of 5a for the End of KS3 Target Level.

I'm not even sure when in a year the end of KS3 is. We're not necessarily all familiar with English Secondary School curricula on this site...

Hi,

Thanks for the reply, I will try and explain this a little clearer if I can. The school levels work in the following way:

A pupil who comes into secondary school (year 7) on a

3c will have to progress through these levels, so from a 3c in year 7 that student should then move on to a 3b and then a 3a in their first year (hence making two sub levels of progress)

And by the time they get the year 9 which is the end of key stage 3 they should have moved from this 3c to a 5c.

3c
3b
3a
4c
4b
4a
5c

I hope this paints a clearer picture. So what I am trying to do work out is if I put the baseline level in can I get a formula that calculates the end of year and end of KS3 level?

Thanks

Originally Posted by FDibbins

Also, what does "2 sub levels" equate to? In your example, 3A plus 2 sub-levels, to me would suggest 3C, but you say 4B?

Hi,

I understand your logic behind converting the letters to numbers but this is currently how we do it and I was just looking at possible solutions to making the system more student friendly and less time consuming.

Levels work in the following way (Low to High)

3c
3b
3a
4c
4b
4a
5c
5b
5a

and so on, so an example of 1 level of progress is moving from a 3a to a 4a and an example of making two sub levels of progress would be moving from a 3a to 4b.

Thanks