Average a list of data with blanks and text

[jlheath01]

I'm looking to find averages based on survey responses. Trouble is, the data is not always complete. There are some blank values as well as text in the data. I'm not sure if I need to clean up the data first or if I can create an average function statement that will look at the "bad" data within the process. I've attached the file that I'm working with.

Each column will be averaged separately. Column B works just fine averaging since there's no bad data. Column C works as well. I'm struggling with columns D & E where there are NAs. Those responses should be counted in the count, but should have a value of 0 in the average function.

Can someone please help me figure out how to get the averages of each column? Much appreciated!

[JohnTopley]

Try

=SUM(D2:D83)/COUNTA(D2:D83)

[jason.b75]

If the count and sum values are correct then average =SUM/COUNT, or =K3/K2

Keep an eye on your blank cells, they appear to contain a null value, which differs from an empty cell, if that changes then your formulas will give incorrect results.

To see what I mean, there are 2 empty cells in D2:D83, double click in one, then press enter, this will clear the null value which causes COUNTA to ignore it, but COUNTBLANK to count it, giving an incorrect result in K2.

[jlheath01]

Thanks Jason.b75. This is my problem. There are inconsistencies between blank and null values. The way the data sits now, I cannot rely on my count and sum values as you mention in your first paragraph.

Is my resolution to clear all null values first or is it possible to use a combination of a SUM/COUNT/IFERROR/ISBLANK formula? If I can do the latter, I'm not sure how.

My total count of responses in all but column F should be 80. I don't get that count consistently using the COUNT function (as you can see in cells K2 and L2).

[jlheath01]

Thanks for the reply, John. However, COUNTA doesn't work for every column. I need a consistent formula that I can copy from column to column. This workbook is much larger than the small sampling of data I posted on this thread.

[jason.b75]

I've been trying this as an alternative method, and haven't found any data types that cause problems so far.

=(COUNT(D2:D83)+COUNTIF(D2:D83,"?*"))

You could also try

=SUMPRODUCT(--(D2:D83<>""))

Both of these will (or at least should) produce the correct counts with blank or empty cells, the first one will also ignore a cell containing a single space if an erroneous one happens to find its way into your sheet.

You should then be able to use that for SUM/COUNT to get the average required.

edit:- changed formulas, original ones involved major common sense failure

[FDibbins]

Just for testing, I created a helper column (O) with this copied down...
=IF(ISNUMBER(D2),D2,"")

I then averaged that column with...
=AVERAGE(O2:O83)
and then comapred that with using the same formula in D...
=AVERAGE(D2:D83)

Both gave the exact same answer, not sure if that is what you were going for?

[jason.b75]

Ford, that will only average numeric values.

Looking at the data in column D
73 numeric values
7 text strings
2 blanks

Using AVERAGE will give SUM/73 (count of numeric values), my understanding is that the requirement is SUM/80 (count of non blank cells, which could be null strings or empty cells).

Something on the lines of

=SUM(D2:D83)/(COUNT(D2:D83)+COUNTIF(D2:D83,"?*"))

Which, using the sample data, will reduce the result from 3.38 to 3.09.

I'm sure there is a dead basic method that I'm missing

[AliGW]

The blank cells are not blank in column D: they have in fact got one space in them (i.e. they are " "), so they are also being counted as text, hence the incorrect counts.

[jason.b75]

Ali,

If you test that with =LEN(D15) you will see that it returns 0, where a single space would still have a length of 1 character.

The cell actually contains a null string, which could be the result of copy / paste, or the format of data from an external source.

Try using copy / pastespecial - values on a formula that returns "", the cell will appear blank / empty but any formula referring to it will still see it as text because of the null value left by "".

Select the cell, then press f2 to enter edit mode, and without deleting anything, press enter, this clears the null value, a space would still be left in the cell.

[AliGW]

Yes, Jason - I see that now - but test it with various counts and you will see that it is skewing them, which is my point, really.

[JohnTopley]

Try

=SUM(D2:D83)/SUMPRODUCT(--(D2:D83<>""))

(based on several previous posts) results in average of 3.0875 (Posts #6 & #8 from Jason)

the SUMPRODUCT results in count of 80.

[jlheath01]

Thank you for all of your help! It turned out that I had to convert my columns to numbers from text in order for the average function to work out as it should. As it turns out, the user who made this request to me did NOT want to use the data that was null/text, etc. in the average formula.