Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 60
Default 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.


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default 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.



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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.




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 60
Default 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
average when one or more cells are in blank art Excel Discussion (Misc queries) 2 August 12th 08 04:53 AM
cells without values causing error message karen Excel Discussion (Misc queries) 6 January 11th 08 07:39 PM
Ignoring blank cells on getting an average Neil Excel Discussion (Misc queries) 6 July 18th 07 08:14 AM
calculating average with blank cells marvinks Excel Worksheet Functions 3 August 7th 06 04:34 PM
Average range including blank cells: #DIV/0! Areaka Excel Worksheet Functions 3 April 12th 06 10:21 PM


All times are GMT +1. The time now is 12:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"