zero value cells/blank cells causing error in AVERAGE?
 

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.

zero value cells/blank cells causing error in AVERAGE?
 

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.

--
Biff
Microsoft Excel MVP


"LilBeanie1033" wrote in message
...
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.

zero value cells/blank cells causing error in AVERAGE?
 

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)

"LilBeanie1033" wrote in message
...
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.

zero value cells/blank cells causing error in AVERAGE?
 

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:A 100))) as an array
formula (Control-Shift-Enter)
--
David Biddulph

"LilBeanie1033" wrote in message
...
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.

Thank you
 

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)

"LilBeanie1033" wrote in message
...
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.

Thank you
 

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)


"lilbeanie1033" wrote in message
...
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)

"LilBeanie1033" wrote in message
...
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.