Problem with averages..
 

Can anyone help am lost with this one!

I have a tracking spreadsheet for student test results. Teachers enter a letter grade and this is converted via VLOOKUP to a number in the next (hidden) column. Some of the letter grades come via linked cells from another sheet in the same workbook. All this works fine... .. I want to have a running average of the results which then goes red or green based on a target grade - again this works fine BUT here is the problem: it only works when all of the grades are filled in, if any are blank cells the average box goes blank too. I have tried various ideas AVERAGE.... doing it mathematically (A2+A3)/4 and COUNTIF etc. but this one is a bit beyond me! Any help more than welcome! Copy attached

Hi Russell,

You'll be glad to hear this is easy enough to sort out.

Taking for example the formula you have in cell I2, change it from =VLOOKUP(H2,AV!$A$1:$B$6,2,FALSE) to =IFERROR(VLOOKUP(H2,AV!$A$1:$B$6,2,FALSE),"") and copy to all relevant cells (Purple ones in columns G to X). That should solve the problem.

Let me know if it doesn't, but it did when I tested it.

Thank you so much! Perfect!!!!!

Russell

Glad to help mate. :)