Averaging data including #N/A

[Guest]

I am having problems averaging a column containing some cells with formula
returning #N/A.
Is there anyway i can average 'around' these #N/A returns, ie average the
numbers but not the text. The #N/A are are the bottom of the column with the
data at the top.

The table is automatically updated from a central location and is the basis
of both charts and printed tables. I have found the #N/A values best when
updating the charts automatically, but found it a pain when i want to average
with returned data!

 

[Biff]

Hi!

Try something like this, entered as an array:
CTRL,SHIFT,ENTER

=AVERAGE(IF(ISNUMBER(B1:B10),B1:B10))

Biff

 

[Biff]

Hi!

Did you enter that formula as an array?

Instead of just hitting ENTER you must hold down CTRL and
SHIFT then hit ENTER.

Biff

 

[Aladin Akyurek]

If #N/A's are guaranteed to be at the bottom of the range, a simpler formula
would be:

=AVERAGE(A2:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

 

[Arvi Laanemets]

Hi

Biff's advise of-course helps, but maybe it's better to avoid formula error
messages at all. When you have a formula
=YourFormula
in some range, ant it returns #NA in some cells, then wrap original formula
into error checking routine, like:
=IF(ISNA(YourFormula),"",YourFormula)
or more general
=IF(ISERROR(YourFormula),"",YourFormula)

Now you can do various calculations on this range, i.e. calculate average,
without any problems.

 

[Biff]

Hi!

The OP said they use the N/A so it won't mess up their
charting ability.

Biff

 

[hgrove]

Kaine wrote...

I am having problems averaging a column containing some cells
with formula returning #N/A. Is there anyway i can average
'around' these #N/A returns, ie average the numbers but not
the text. The #N/A are are the bottom of the column with the
data at the top.

...

If your #N/A values would always be grouped, Aladin has alread
provided the most appropriate solution. However, for data with #N/
appearing anywhere, here's a nonarray formula alternative.

=SUMIF(Range,"<>#N/A")/COUNT(Range