Thank you FloMM2 for your help I have pasted ypur formuls on to the end of
mine so now it reads:
=IF(AI13&AW13&BI13&BW13&CJ13="N","",IF(AI13&AW13&BI13&BW13&CJ13="R","",SUM((AI13,AW13,BI13,BW13,CJ13)/500)*"100"))
...but I'm still getting that #Value thing in cell L13

Cheers
David

Hi David

If you don't want an average calculated if there is an "N" or an "R" in
your range of cells, then one method might be to just ensure that you
have 5 numeric values
=COUNT((AI13,AW13,BI13,BW13,CJ13)
will return the number of numeric values in your range

If you wanted to produce a zero result if "N" or "R" or a Null value
appeared, then you could use
=AVERAGE(AI13,AW13,BI13,BW13,CJ13)*(COUNT((AI13,AW13,BI13,BW13,CJ13)=5)

If the count function returned 5, then the result would be True, and the
multiplication of the two functions together would coerce the value of
True to 1 so the result would stand.
If the value of Count() was less than 5, the result would be False,
which would be coerced to Zero so the whole result would become 0.

Note.  This would not produce a result if there were any blanks cells
within the range.

If you are happy to have an average based upon less than 5 results,
provided there are no text characters in your range, then you could use
a test including COUNTA.
=COUNTA((AI13,AW13,BI13,BW13,CJ13)
will return the total of all cells with content, either numeric or text
values

In this case you would need to multiply your Average by (counta() =
count())

=AVERAGE(AI13,AW13,BI13,BW13,CJ13)*
(COUNT((AI13,AW13,BI13,BW13,CJ13)=COUNT((AI13,AW13,BI13,BW13,CJ13))