Dai,
1. did you test the 0 result on column M, cause this will be included in
your averaging even there is no data (blank) on H8 Yet. and it cannot be
hidden inside an averaging formula.
2. your post requires a conditional formula (value between 1 and 100)

i hope your post can clarify the real formula needed, so other viewers can
learn also.
happy thanksgiving..

"DAI" wrote:

Driller,

1 You are right regarding the incomplete data but as the result is used only
as a rough guide to order alternative material i have used conditional
formatting to hide the errors.

2 I used the ctrl+Shift+Enter and have the{} around the formula but still get
the #DIV/0! Error

Also the or =sum(M8:M5000)/count(M8:M5000) formula would have worked well
(just me over complicating things)


Thanks

Driller,

1 You are right regarding the incomplete data but as the result is used only
as a rough guide to order alternative material i have used conditional
formating

driller wrote:
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.

Hello,

[quoted text clipped - 19 lines]

Any Help would be appreciated.