sorry Dai for being confused
but please take a look at your Question thread "Value between 1 and 100"
which is a specific conditional question..
You will have 0 result on M8, when L8 is blank and H8 has no data yet ?
Average will fail, even for the viewers.

try to check it so viewers will not be confused.


"DAI" wrote:

Sorry all,

Im getting a bit confused here,

The solution posted by Don is the only one that works.

=AVERAGE(IF(ISNUMBER(M8:M5000),M8:M5000,""))

with the Ctrl+Shift+Enter.

Thank you all.

DAI wrote:
Hello,

I need to show the average of a column (cells M8 to m5000)

As this column is populated by a formula =(L8/H8)*100

And in turn this is populated by the data entered into L8 and H8.

------------------------------------------------------------------------------
----------------------

My problem is - the #DIV/0! error (caused by the first set of data in the
chain not being entered yet)

Therefore the simple AVERAGE function can not be applied.

I was playing with the idea of

=average(if(M8:M5000)<100)

but this is not a valid formula.

Any Help would be appreciated.