How to write AverageIf (Not an Empty String) in Google Sheets?

Question

I have the answer to this question. However, it took me a few wasted hours of research and work to figure it out. I couldn't find an answer anywhere online, so I thought I'd write out a question and share the answer in the hope of saving someone (who is as dumb as me) some time.

I've been working on a Google Spreadsheet for a while to get attendance counts for a few classes. I have a Google Form where teachers input the attendance for their class. That automatically gets sent to a Form Response spreadsheet. From there I have a few sheets that manipulate the input data, then a summary sheet that shows a table of: classes (rows), dates (columns), and attendance to each class on specific dates. Each cell in the table contains a formula which either returns the attendance count or an empty string. Until data exists for a date and class, that cell contains and empty string. The table works perfectly.

Using the data in this table, I want to get average attendance for each class in real time. There are a couple of problems though:

1. The table is set out for the whole year, so there are a lot of cells containing empty strings until the end of the year, which mess up the "real time" average
2. Occasionally a class's attendance doesn't get filled in, so there are random cells with empty strings which mess up the average throughout the year

I've been trying AVERAGE, IF, and AVERAGEIF functions to figure this out. AVERAGEIF seems to be the solution but I can't figure out a criteria that works. "<>""" causes a return value of 0 no matter what range I'm trying to average. I've thought that using ISNUMBER(parameter) as the criteria, but I don't know what to use as a parameter for it. Not surprisingly, using it without a parameter didn't get me anywhere (DIV/0 error)

I haven't found Google's Docs editor Help to be super helpful, so I was wondering if anyone might be able to share how to use AVERAGEIF(does not contain an empty string).

Answer 1

=AVERAGEIF(A1:Z1,"<>")

"<>" on its own means "not equal to an empty string" in Google Sheets.

Answer 2

I recommend using:

=IF(COUNT(E4:G4);AVERAGE(E4:G4);0)

COUNT() returns numbers of cells containing valid numbers : if none, the value will be 0, so the condition will evaluate to false and return 0 (you can put "" instead of 0 if you prefer). Otherwise, it will use the result of the AVERAGE function.

Answer 3

To all future viewers, easiest way is Sum(A1:Z1)/Counta(A2:Z1)

Answer 4

I also encountered (DIV/0 error) when attempting to use AVERAGEIF to check for empty string. In my case, I only want to take an average if a text column is non-empty, as the number columns will always be populated in my sheet but are only valid if the text column is populated.

My solution is to use an IF function:

=IF(A2 <> "", AVERAGE(D2:AF2), "")

This is what I'd expect AVERAGEIF to do on it's own, but it doesn't.