Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
average when one or more cells are in blank | Excel Discussion (Misc queries) | |||
cells without values causing error message | Excel Discussion (Misc queries) | |||
Ignoring blank cells on getting an average | Excel Discussion (Misc queries) | |||
calculating average with blank cells | Excel Worksheet Functions | |||
Average range including blank cells: #DIV/0! | Excel Worksheet Functions |