You might have zero values in the range "score", and if so the normal
AVERAGE function would count these and give you a lower average. Hence
the AVERAGE(IF part of the formula which only counts values that are
above zero. There is further checking in the formula so that it only
returns an average (of above-zero items) as long as there are no
errors in the range (or indeed if all values are zero, so there is no
average to return).

It needs to be an array formula so that it can check each value in the
range to see that it is above zero.

Hope this helps.

Pete

On Dec 8, 4:34*pm, Diddy wrote:
Hi,

I used the following in a spreadsheet over a year ago and can't remember
how, why, or what I've done. Head spinning because I'm making unexpected
changes which were needed "Now, if not sooner"
The formula below is entered as an array formula but don't know why
{=IF(ISERROR(AVERAGE(IF(Score0,Score))),"",AVERAG E(IF(Score0,Score)))}
Named range "Score" is a dynamic range as below
=OFFSET('Sheet1'!$Q$1,0,0,COUNTA('Sheet1'!$Q$1:$Q$ 35),1).
If anyone can help I would be grateful :-)

Cheers
Diddy