School Tracker- Average needed for Lookup Table

[kevwilsondj]

Hello,

I am a teacher in Scotland trying to create a tracking and monitoring spreadsheet. However, I have reached a point in this spreadsheet where I am completely stuck. I have asked around but no-one seems to be able to crack the code for this one, and so I turn to you guys in my final bid to solve this.

Basically the children get assessed on their writing throughout the year: August, January and May. The children then receive a grade for their writing. The grades are as follows:
E1
E2
E3
E4
E5
E6
F1
F2
F3
F4
F5
F6
S1
S2
S3
S4
S5
S6
T1
T2
T3
T4
T5
T6

What I am hoping to do is create part of my tracking and monitoring spreadsheet where the class teachers add in the grades for each child as the year progresses. But I then need to find an average grade for each child (which will then ping through to a separate page- but it's ok I think I know how to do this one).

I believe I need to create a lookup table so that each grade represents a value. This allowing the computer to know that F1 is better than E6. However, I don't have a clue how to do this.

The trickier bit yet is how to find an average value of the grades and represent this as a grade eg. F4

I have included an attached sheet that may hopefully provide some clarity to what I am trying to do.

Within the spreadsheet you will find a tab titled 'Screening Raw Data'.

The P1 teacher will input data into columns H, I and J. But I need the computer to then average the grades into column K.

So far I have had some support from other people but I don't know how to transfer their knowledge and advice into this table.

I've included a possible answer to this formula provided by someone else.

Please, Please can someone try to input this into my spreadsheet... in doing so you will be helping me and the 350 children at my school!!

Hugely appreciated!!!

[Jack7774]

Try this one out. Its a formula that looks up each letter number value then rounds what they average and then look up that number as well to pull the letter and number back to its original form.

[Zodeeak]

Try the attached.

Hope that helps.

-Z

[etaf]

that appears to be a good solution provided
And you would probably need to reproduce the possible solution as a separate sheet

is it only column H, I , J
if so
here is the example changed to your sheet

BUT the average is 12.666 and so rounding up gives 13
is that the grade you want S1
or 12 which is F6

[kevwilsondj]

Thank you so much this is a huge help. Thank you also to the other posts that have provided a solution to this. Yeah I would need anything less than .5 to round down and similarly .5 and above to round up.
I'm planning on creating an average column for the other classes (P2 to P7) and copying the formula into those cells also. Would this simply be a copy and paste job?

Any chance you could make a slight change to your solution: Class teachers will be updating this spreadsheet throughout the year- they will not be inputting all three grades at one time. Therefore, I need it so that even if the teacher has input one grade then in the average column it will try to average that one grade- obviously just showing the same grade as what was input for Aug. Similarly if they were to input only two grades (August and January) then I need it to average just the two.

Hope I have explained this ok?

[Jack7774]

In that case Etaf has the solution for you.

[etaf]

which solution are you referring to for the modifications ?

[kevwilsondj]

sorry- your solution etaf. I'm new to this forum business. It is working a treat though. I've spent weeks trying to solve this puzzle.

[etaf]

i have added a IFERROR() to the sheet1 formulas and also to the result
so that if there is no entry then it will just leave the cell blank and so should now also average correctly

[kevwilsondj]

Thanks etaf.

I'm very sorry to be a pain but how do i now apply this formula to the other cells.

I tried clicking on the small plus sign bit at the bottom of cell K6 and dragging down for rest of column but does not seem to work.

I also need to apply the formula to other columns such as 'W'. How would I go about doing this.

I very much appreciate the time you are taking to solve someone elses problems. Thank you

[kevwilsondj]

sorry forgot to add this

[etaf]

Ok - so now we know you are happy with the round - we can put that into the cell K instead of L on sheet1

I tried clicking on the small plus sign bit at the bottom of cell K6 and dragging down for rest of column but does not seem to work.

seems to work for me
I have added another if error to avoid the div 0 error

Now all the columns can line up with all the other columns in your raw data

So in Column H on sheet1
=IFERROR(INDEX($B$2:$B$25,MATCH('Screening Raw Data'!H6,$A$2:$A$25)),"")
we are referring the same column and row H6

so you should be able to copy that to column L
and change to
=IFERROR(INDEX($B$2:$B$25,MATCH('Screening Raw Data'!L6,$A$2:$A$25)),"")

so that change will apply to all the column groups

L.M,N
P,Q,R
T,U,V
Dont seem to be the correct columns - looking at the sheet closly
as you do not seen to have the same setup as the column H,I,J with the average in K

so you just copy and change the column ID

Then on the Raw sheet
=IFERROR(INDEX(Sheet1!$A$2:$A$25,MATCH(Sheet1!K6,Sheet1!$B$2:$B$25),0),"")
so instead of Sheet1!K6
you change to the column for that group

What are the next cells to apply and if column T
do you need to add an average column now ?

[etaf]

just seen you last post

So i have updated the formula for column
T,U,V,W
on sheet 1
and on the raw sheet

[kevwilsondj]

school tracker 4.xlsxI can't thank you enough etaf.

I am very nearly there.

I looked at what you had created and I tried to copy that for the rest of the classes. However, my formulae have not worked. I can't understand why not?

Please can you have a look at my work and try to spot my error.

Thank you

[etaf]

i have added all the formulas to sheet1 and also added a few samples in the
rows 50-60
so you would need to clear those on the input sheet -Just the grades entered - NOT the formulas

see how that works out

I also cleared the headers and put a block line around the sections

I copied the formulas down on sheet1 to row 230
on the RAW sheet it only goes down to row 229
so thats covered
BUT
if you do go down the raw sheet further than 230 - all the formulas on sheet1 will need to be copied down
Are you going to use more than 229 rows on your raw sheet - if so best to copy them down NOW ?

Also try out a few tests across the ranges and make sure the averages are giving you correct/expected results

[kevwilsondj]

Thank you so much Etaf!!! You have been a huge help in supporting me to crack this one.

Thank you.

[etaf]

your welcome - thanks for the rep