Calculate AVERAGe with #MISSING!

[Arne Hegefors]

Hi! In Excel I try to calculate AVERAGE by using the normal function
=AVERAGE(cell1:cell2). However if I have a cell that says #MISSING! the
average also becomes #MISSING!. I have asked this question before but then I
got the answer that Excel is able to deal with that and that I should be able
to calcualte the average anyway. That is not the case with my Excel (and
neither for my coworkers so it is not just my computer).

I have a Swedish Excel (and yes I write MEDEL instead of AVERAGE..) version
2003 (part of Microsoft Office Professional Edition 2003).

I would be most grateful for any help. This is not just related to
calculating AVERAGE but all sorts of quick things you want to do. I know
there are ways to gop around it (like search and replace) but that takes
time. PLs help! Thanks!

 

[Mike H]

Maybe this

=AVERAGE(IF(ISNUMBER(A1:A4),A1:A4))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


Mike

 

[Ashish Mathur]

Hi,

You can try this as well

SUMIF(range,">=-9.9999E307")/COUNTIF(range,">=-9.9999E307")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com