Average result getting skewed by zero's

[mykeee]

I am working on a spreadsheet where I need to determine the average of 12 cells.

=AVERAGE(E19:E30)

The contents of these 12 cells (worksheet A) are pulled from another worksheet (B), and if worksheet B does not have data in each of the 12 cells, the blank cells display a zero in the corresponding cell of worksheet A.

This poses a problem for the formula above as it averages 12 data points regardless of how many data points are entered into worksheet B. If there are only 6 data points, I need the average of the 6 data points, not 6 data points plus 6 zeros.

Anyone have ideas on how to resolve this?

 

[barry houdini]

In Excel 2007 or later

=AVERAGEIF(E19:E30,">0")

in earlier versions

=SUM(E19:E30)/COUNTIF(E19:E30,">0")

 

[Andrew Poulsom]

Can you change your formula to return "" if the source cells are blank? Null values will be ignored by the AVERAGE function.

 

[taurean]

What version of Excel are you using?
Excel 2007+
=AVERAGEIF(E19:E30,"<>0",E19:E30)
Excel 2003 -
=SUM(E19:E30)/COUNTIF(E19:A30,"<>0")

 

[mykeee]

In Excel 2007 or later

=AVERAGEIF(E19:E30,">0")

in earlier versions

=SUM(E19:E30)/COUNTIF(E19:E30,">0")

Sorry, I should have told you I am using Excel 2003.

I tried the =SUM(E19:E30)/COUNTIF(E19:E30,">0") and it appears to work... However, I just realized something that complicates this a bit. What if the data truly is zero? In some cases it may be a valid result. I don't want those results to be ignored.

 

[mykeee]

Can you change your formula to return "" if the source cells are blank? Null values will be ignored by the AVERAGE function.

How would I go about doing this? Currently the formula looks like this:

=Data!E19

 

[Andrew Poulsom]

Try:

=IF(ISNUMBER(Data!E19),Data!E19,"")

 

[taurean]

=IF(ISBLANK(Data!E19),"",Data!E19)
Edit: Please use Andrew Poulsom's formula for its better argument for numbers!

 

[mykeee]

Try:

=IF(ISNUMBER(Data!E19),Data!E19,"")

Works great Andrew! Thanks!

I have another question now that stemmed from fixing this problem.

I had conditional formatting on the cells E19 to E30 that turns the cell background red if either of the two conditions are met:

Formula Is : =ISTEXT(E19)

Cell Value Is: Greater Than 10

Now this was working prior to this latest fix, however, now if I have a null value returned to one of those cells it turns red even though it is neither text or greater than 10.

Any ideas what's going on here?

 

[Andrew Poulsom]

The null value is text. The COUNTBLANK function treats it as blank.