School tracking- how to average colours

Hi,

I am a depute head teacher and have created a spreadsheet to track pupil progress with their learning.

Although my skills within excel are pretty limited, I have watched numerous video clips and followed tutorials to get me to the stage I am at now. However, what I require now is for my spreadsheet to show an average level of where a child is at with regards to an aspect of their maths. Please see the tab titled 'Maths Detailed' column K.

Under the heading 'Number, Money & Measurement' there are subcategories where the teacher would identify the level (by colour)( that the child is working at. I need the spreadsheet to find an average for these colours (rounding up where necessary). I have assigned a lookup table for each colour so that a value is assigned to each- not sure what next.

I had been given a formula for this once before, however I have since adapted the spreadsheet and the previous formula needs adapted. I think the previous formula will just require some tweaks but unsure as to how to do this.

The previous formula was:
=IFERROR(INDEX(Sheet2!A:A,MATCH(ROUND(AVERAGE(INDEX(Sheet2!B:B,MATCH('Maths Detailled (2)'!E4,Sheet2!A:A,0)),INDEX(Sheet2!B:B,MATCH('Maths Detailled (2)'!F4,Sheet2!A:A,0))),0),Sheet2!B:B,0)),"")

Please help. Your support will go a long way to helping myself and my colleagues (not to mention the hundreds of pupils)!

Thanks,
Kevin