Google-sheets – How to write AverageIf (Not an Empty String) in Google Sheets

google sheetsworksheet-function

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).

Best Answer

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

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