Ignoring non numerical data in calculating an AVG

[Prof_Ann]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)

I need to get an average value from a group of cells in which there may, at times, be non numerical values ( i.e. a letter instead of a number). The way I have done it (the "usual" way), when there is a non numerical value, I get a ##### instead of the AVG I need. Anything I can do? I'd really appreciate the help!!!

 

[CyberTaz]

What function are you using? I don't know what you mean by "the usual way",
by *my* usual way is;

If I have a column of:

A1 = 2
A2 = A
A3 = 2
A4 = B
A5 = 2

And in C1 I put: =AVERAGE(A1:A5)

it returns a value of 2 because the AVERAGE fx ignores any cells in the
range which don't contain values & divides by the number of values found.

Are you saying it doesn't work that way for you, are you trying to have the
text parsed as values, or is there something else you're wanting to
determine?

 

[Bob Greenblatt]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)

I need to get an average value from a group of cells in which there may, at
times, be non numerical values ( i.e. a letter instead of a number). The way I
have done it (the "usual" way), when there is a non numerical value, I get a
##### instead of the AVG I need. Anything I can do? I'd really appreciate the
help!!!

If you get #### that means the column is not wide enough to display the
result. Change the column width, or change the format to display fewer
significant digits.