zero value cells/blank cells causing error in AVERAGE?

  • Thread starter Thread starter LilBeanie1033
  • Start date Start date
L

LilBeanie1033

I am attempting to build a running daily score average report based on
several categories that either have a numeric or N/A value. How do I omit
the N/A values from the average? I can't seem to make it stop assigning a
zero value to those cells.
 
If the N/A is a TEXT entry AVERAGE will ignore it. If it's the Excel error
#N/A try this array formula** :

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

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
If the only error values would be #N/A..
try this regular formula:
=SUMIF(A1:A10,"<>#N/A")/COUNT(A1:A10)

Adjust range references to suit your situation.

Is that something you can work with?

Regards,

Ron Coderre
Microsoft MVP (Excel)
 
I'm surprised if your AVERAGE assigns a zero value to the N/A cells; I
would expect the AVERAGE to return N/A.

Try =AVERAGE(IF(ISNA(A1:A100),"",IF(A1:A100="","",A1:A100))) as an array
formula (Control-Shift-Enter)
 
One more question - In reference to this formula, how would I also omit a
blank cell? It's a chronological spread, so if I am at the beginning of the
report cycle, there will be areas not yet scored. I'd like my average to be
"to date". I hope that makes sense.

Your input is really helpful - thank you!


If the only error values would be #N/A..
try this regular formula:
=SUMIF(A1:A10,"<>#N/A")/COUNT(A1:A10)

Adjust range references to suit your situation.

Is that something you can work with?

Regards,

Ron Coderre
Microsoft MVP (Excel)
 
You weren't explicit...but, I'm guessing that:
1) values are calculated or true blanks
2) zeros, #N/A!, and blanks should be ignored

If that's true, try this regular formula:
=SUMIF(A1:A10,"<>#N/A")/COUNTIF(A1:A10,">0")

Does that help?

Regards,

Ron Coderre
Microsoft MVP (Excel)
 
Back
Top