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?

 

[mykeee]

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

Ok, let's see if I understand this correctly:

Is this what you are proposing?

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

I tried this and it returns a zero when the source cell is blank.

 

[Andrew Poulsom]

I was proposing that use use COUNTBLANK in your conditional formatting.

=AND(OR(ISTEXT(E19),E19>10),COUNTBLANK(E19)=0)

 

[mykeee]

I was proposing that use use COUNTBLANK in your conditional formatting.

=AND(OR(ISTEXT(E19),E19>10),COUNTBLANK(E19)=0)

I think I'm going around in circles. Every solution comes with another problem.

The conditional formatting you proposed worked, however I've discovered another issue. Maybe I'll take a step backward and more fully explain what the spreadsheet is used for.

I have some test equipment that generates a report. The report (.rtf document) usually consists of numerical values, however from time to time there is an error in the form of text. The results of this report are copied and pasted into the "DATA" worksheet of this spreadsheet. Cells E19:E30 from the "FORM" worksheet (12 data points) pull the appropriate data from the "DATA" worksheet. In the event that there is an error (text) in one of the 12 data points I would like the conditional formatting to turn the cell red on the form. Additionally, if one of the data points is above 10 I would also like it to turn red on the form. Cell E31 on the form is the average of any data points from cells E19:E30. If there are less than 12 data points there must not be a zero returned to one of these cells as it would skew the average result. Also it is possible that one of the data points could be zero, so we can't just ignore values equal to zero as this may be a valid data point.

So what is happening now is:

Cell E31 has this formula:
=AVERAGE(E19:E30)

Cells E19:E30 have this formula:
=IF(ISNUMBER(Data!E19),Data!E19,"") Of course the cell reference is different in each cell

Cells E19:E30 have the following conditional formatting:
=AND(OR(ISTEXT(E19),E19>10),COUNTBLANK(E19)=0) Of course the cell reference is different in each cell

Now it seems that if there is anything other than a numerical value in the Data worksheet, the cell in the form is completely blank. This is good if there were less than 12 data points in the data worksheet then there is no longer zeros in the form (which would mess up the average). However if there was an error message in the report (text) it will not show up on the form, and I need this to happen.

I appreciate the help you guys are giving me. Sorry if I'm being a pain in the butt!

 

[mykeee]

I'm pretty sure I got it figured out now.

In cells E19:E30 I changed the formula to:
=IF(ISBLANK(Data!E19),"",Data!E19) as per taurean's suggestion

I then changed the conditional formatting on these cells to:
=AND(OR(ISTEXT(E19),E19>10),COUNTBLANK(E19)=0)

This appears to have done the trick.
1. When there is a text error in these cells it turns red.
2. When the source cell is blank, this cell is blank and does not turn red and does not skew the average.
3. When the source cell is zero, this cell displays a zero and is included in the average.

Thanks everyone for their help!