4

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

migrated from superuser.com Feb 15 '17 at 21:27

This question came from our site for computer enthusiasts and power users.

  • Did you try just "<>" or "<>" & "" – Scott Craner Feb 15 '17 at 20:31
  • 1
    "I have the answer to this question" - that's nice but it's off topic. Please read the google-spreadsheets tag ... – DavidPostill Feb 15 '17 at 20:50
  • If you set the datatype for the column, wouldn't this problem disappear? – Yorik Feb 15 '17 at 20:54
  • Thanks for the guidance @David. By the time I was adding tags I was having some computer speed issues and wasn't really paying attention. I saw a few other google spreadsheet questions on the site, so assumed it was fine. Now I know! – Alamb Mar 1 '17 at 5:45
6

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

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

  • @pnuts Yes, I did. It kept including all the blank cells in the calculation as if they were zeroes. – Alamb Mar 1 '17 at 5:47
  • 1
    That's weird, I've just tested the formula on a range that includes blank cells and they were not counted. Maybe you could post a link to the spreadsheet where it occurs so that the problem could be debugged? – ttarchala Mar 14 '17 at 16:15
  • 2
    It's weird it deosn't work for me.. =AVERAGEIF(F2:N2,"<>") all the cases between F2 and N2 are cells with data validation, based on constantes of another page. Only one is filled, so it should give 1/9 percent – Emixam23 Jul 14 '18 at 12:46
0

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.

-1

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

  • 1
    Hey! Welcome to Web Applications! You should expand on your answer for it to be really helpful. What does the cells in you example relate to? – Punchlinern Feb 28 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.