Quote:
Originally Posted by Russell Pearce
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.