Excluding Blank Cells when using =Average

[commodoros]

I am having a difficult time figuring out how to create an =Average formula that excludes blank cells.

The situation is thus:

I have to create a report everyday based upon a variable number of data entries. Some days there will be 5000 pieces of information. Some days 500.

I want to create a formula that will automatically average several different pieces of information automatically once it is imported from a .csv file.

I need the formula to average the information in column C, but only if it meets the criteria in column A.

A formula like this works when the array is concretely defined
=AVERAGE(IF(A2:A100="East",C2:C10))

But I need to make it work without a concretely defined column C, because as I mentioned earlier, somedays column C will have 5000 entries and somedays it will have only 500.

I would like it to work like this
=AVERAGE(IF(A2:A100="East",C:C))
or
=AVERAGE(IF(A2:A100="East",C2:C10000))

But I can't make it work. Any advice would be greatly appreciated.

 

[cfree36]

How about:

=SUMIF(A2:A10000="East",C2:C1000)/COUNTIF(A2:A10000="East")

 

[Greg Truby]

Use dynamic named ranges. Here's my "boilerplate" on the subject...<hr />

Dynamic Named Ranges

Dynamic named ranges can be used just like normal named ranges in formulas, or as sources for things like pivot tables and charts. However instead of a normal named range where the group of cells being referred to is static, dynamic ranges typically take advantage of the Offset() and CountA() [or Count()] functions to create a range that resizes to include new data appended to the bottom of the range.

To Create a Dynamic Range

1. From the menu Insert | Name | Define...
2. In the Names in Workbook textbox, type in the name for the Named Range - probably something like "Data" or "DataRange".
3. In the Refers to textbox type in a formula like the following (note: you may find it helpful to hit the F2 key while in the textbox to put it into "edit mode"):<ul type=square>
4. =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),9) to start at A1, include all non-blank rows and 9 columns. (Good example for pivot tables where you want to include headers.)
5. =OFFSET(Sheet1!$B$2,0,0,COUNT(Sheet1!$B:$B),1) to start at B2, include all numeric rows and 1 column. (Good example for a chart data series.)
6. =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1) to start at A2, include one less than the count of all non-blank rows and 1 column. (Good example for a chart category series where values are not numeric.)

[*]You're ready to reference the Named Dynamic Range like you would a normal Named Range.[/list]

 

[commodoros]

Making the range dynamic doesn't seem to chyange the fact that it won't exclude the blank cells when attempting to calculate the average. If I create a dynamic range that includes even one blank cell in the calculation, Excel returns a #N/A error.

I am perplexed. Surely this is possible. It seems like this would be a fairly common need when performing calculations.

 

[cfree36]

Did you try the sumif / countif method.
This works, and excludes the blank cells.

You can use the dynamic ranges instead of the stated range.

 

[cfree36]

You could also use this array using the dynamic ranges to exclude zeros:

{=AVERAGE(IF(C6:C11>0,C6:C11))}

Don't type the { or } just hit Ctrl + Shift + Enter after typing =AVERAGE(IF(C6:C11>0,C6:C11))

Chagne the stated range to your dynamic named range.

 

[cfree36]

Change >0 to <>"" to exclude blanks...

 

[Greg Truby]

Sorry, since cfree posted one of the better formulas for skinning this cat I assumed you'd mix both posts. Combine DNR's with his formula and you should be good to go.

 

[commodoros]

Thanks for your help guys. This is the formula that ended up working:

{=AVERAGE(IF($A$2:$A$10000="east",$C$2:$C$10000))}

 

[Greg Truby]

Perhaps I'm overlooking something? But how does that differ from what you posted originally? Looks like you've just oversized the reference to "be sure" it's big enough for whatever comes along.