1. better revised the formula on M8 because
there are two type of unwanted result that will appear due to Incomplete
Data, either 0 or #DIV/0!...the 0 result will be counted in your averaging
formula.

=IF(AND(H8="",L8=""),"No Data",IF(OR(H8="",L8=""),"Data
Incomplete",(L8/H8)*100))

2. averaging formula of a Complete data only.
=average(if(M8:M5000<100,M8:M5000)
press Ctrl+shift+enter on edit mode, excel will insert the braces { } for a
verified array formula.
take care since the {=average(if( ))} will count even the blank cell for
its average.

or you can clasically use / type in
=sum(M8:M5000)/count(M8:M5000)

these averaging formula will work if you use the suggested If formula on 1.

"try to correct typos for better understanding and learning"
good luck.


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