=IF(ISERROR(M8:M5000),"",AVERAGE(IF((M8:M50001)*( M8:M5000<100),M8:M5000)))

ctrl+shift+enter (not just enter)


"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.