Hi Luke,

Many thanks for your reply :-)

Please ignore previous post. When I stopped panicking and let the brain work
(it does sometimes), I realised that you had answered all the question :-)

Thank you again and thanks for the alternative formula

Cheers
Diddy

"Luke M" wrote:

You are doing the equivalent of an "averageif" by having IF function sort
through your Score range and only outputting data that meets your criteria.
This is why you needed an array. The ISERROR is there in case you have zero
data points meeting criteria.

However, a shorter, non-array formula would be:
=IF(COUNTIF(Score,"0")=0,"",SUMIF(Score,"0")/COUNTIF(Score,"0"))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"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