Sum Of Cells in a Column

[Shaijuzacharia]

How can I get the sum of data in a column if there are non number
elements too?

FOr example,

I have got a column with numbers and #NA. When I take the sum or
average, #NA is diplayed. Instead I want to get the actual average of
numbers alone.

 

[Bob Phillips]

=AVERAGE(IF(NOT(ISNA(A2:A200)),A2:A200))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.


But wouldn't it be better to correct the #N/As?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)