Foumulas working off incomplete figures

Evening,

I'm trying to set up a grade template sheet at the start of the year. The problem is as the year goes on, more students arrive so it is causing me problems with the formulas.

On my spreadsheet in R1 I want to work out the average score for the results in column P. So I'd usually use the formula =Average(P2:P8), however as I know more students will arrive I'm trying to get it to look all the way down the column.

However if I try anything like =Average(P2:P400) or =Average(P:P) it comes up as an error. Is there any way around this please?

Many thanks

Hi Cmorgan;

Since you're using Excel 2010, they've introduced an Averageif function. Assuming you don't want to penalize people for tests they weren't around for, you can do something along the lines of
=AVERAGEIF(P2:P8,">0")

Scratch that... only read the question, and did not review the attached spreadsheet... I will rework with a better, more fitting answer...

Alan beat me to it!
The average function will not count the null values from the If iserror code function

Put this in P2 and copy down

=IF(ISERROR(CODE(N2)-CODE(O2)),"",(CODE(N2)-CODE(O2)))

Alan