AVERAGE help, Excel 2003

[It's the Principle!]

I have an Excel problem again. This time, it's with AVERAGE.
My project is faculty evaluations and I have spreadsheet listing the
rating areas in column A, stopping every few rows with a formula for
the average on the area, such as teaching skills, communication skills,
and operating room experience. Columns B and beyond are the individual
ratings given by the residents. The last column is the average/rating
for each question/row.

There is an instruction that if they are unable to give a rating due to
inadequate exposure, they should enter "#." The last formula for the
overall average from all the residents is written to ignore "#" and it
does... except for when this happens:

One of the ratings almosy always gets "#" because they don't go into
the OR (or whatever, I can't think of what it is right now). The
function AVERAGE on the rows and columns works just fine if they have
"#" in it along with actual numbers, but with these doctors who only
get "#" on that rating/row, it gets a DIV/0 error. With that error in
that spot, the final formula won't work and gives a DIV/0 error, too.

The final formula is built to ignore "#" and if I plug in the symbol I
get my averages. But I don't want that to be a permanent solution
because there may be a time when one of the doctors does provide
exposure to that area and they'll get a score, but I'll forget to put
the formula back in and it will throw everything off.

I've tinkered with it eight ways from Sunday but cannot manage to come
up with the right way to say, "If everything in this row is #, then
enter #. Otherwise, average everything that is an actual number and
ignore the rest."

Can anyone help?

 

[T. Valko]

You haven't said what the possible ratings are. Is it possible to have all
ratings of 0? Is 0 even a possible rating?

With that being said, all you need to do is check for *any* numbers but
that's where all 0s could cause a problem.

If you had: #,#,#,0,0,0,#

Then just checking that there are *any* numbers won't work. You'd still get
a #DIV/0! error.

If all 0s is not possible then this should work:

=IF(COUNT(A1:J1),AVERAGE(A1:J1),"#")

Biff

 

[It's the Principle!]

You haven't said what the possible ratings are. Is it possible to
have all ratings of 0? Is 0 even a possible rating?

With that being said, all you need to do is check for *any*
numbers but that's where all 0s could cause a problem.

If you had: #,#,#,0,0,0,#

Then just checking that there are *any* numbers won't work. You'd
still get a #DIV/0! error.

If all 0s is not possible then this should work:

=IF(COUNT(A1:J1),AVERAGE(A1:J1),"#")

Biff

The ratings are 1 thru 4 or Unable to Evaluate (#). So I could have
#,#,#,# all the way across on certain doctors, but no 0. And the
above formula doesn't work because when I put a number it in, it
still returns #.

Any other ideas?

 

[Ragdyer]

Biff's formula works fine using manually keyed in values.

Is your data keyed in, or are they the returns of formulas, or are your
entry cells perhaps pre-formatted to Text, or maybe the data is imported as
text from other documents?

 

[T. Valko]

The ratings are 1 thru 4 or Unable to Evaluate (#). So I could have
#,#,#,# all the way across on certain doctors, but no 0. And the
above formula doesn't work because when I put a number it in, it
still returns #.

Any other ideas?
=IF(COUNT(A1:J1),AVERAGE(A1:J1),"#")

the above formula doesn't work because when
I put a number it in, it still returns #.

If that formula doesn't work then the cells must be formatted as TEXT. A
text number and a numeric number are different and TEXT numbers don't work
in most formulas.

Format the range of cells as GENERAL then re-enter the data or edit the
cell. To edit a cell (after you have changed the format to GENERAL) just
double click in the cell and press enter. Or, select an empty cell somewhere
(a cell that hasn't been formatted as text). Copy that empty cell. Then,
select the range of cells that contain your #'s and numbers. Then do
Edit>Paste Special>Add>OK.

Biff

 

[It's the Principle!]

Biff's formula works fine using manually keyed in values.

Is your data keyed in, or are they the returns of formulas, or are
your entry cells perhaps pre-formatted to Text, or maybe the data
is imported as text from other documents?

I found my mistake when I tested his formula. It works.

FYI, the ratings are keyed.

 

[It's the Principle!]

If that formula doesn't work then the cells must be formatted as
TEXT. A text number and a numeric number are different and TEXT
numbers don't work in most formulas.

Format the range of cells as GENERAL then re-enter the data or
edit the cell. To edit a cell (after you have changed the format
to GENERAL) just double click in the cell and press enter. Or,
select an empty cell somewhere (a cell that hasn't been formatted
as text). Copy that empty cell. Then, select the range of cells
that contain your #'s and numbers. Then do Edit>Paste
Special>Add>OK.

Biff

That's exactly what happened. Sorry. My apologies. I did not mean
to besmirched your Excel Godship.