AVERAGEIF(column,"<>0) giving error #DIV/0!

[msmith3]

I have a table with 20 rows and only 6 rows have data in them. I'm trying to get the average of only the rows containing data in a column. Can anyone help me figure out why I'm getting an error on this formula?

Example:

Sam	5
Jess	2
Dean	4
Michael	4

 

[Eric W]

Glad we could help!

 

[msmith3]

Follow-up question: if one of the cells that I'm averaging contains an error such as "#VALUE!", how can I still find the average? I'd like the formula to be the same for each column, whether they have zeros, errors, or no problems.
Would this formula do that? =IFERROR(AVERAGEIF(Table[Q1],"<>0"),AGGREGATE(1,6,Table[Q1]))

 

[Eric W]

Are you going to have any actual 0 values in that column that you want to skip? As opposed to empty cells? If not, just use:

=AGGREGATE(1,6,Table[Q1]))

If you do have actual 0's that you want to skip, then your proposed formula would include them if there were another error in the column. You'd need to do something like this:

=AVERAGE(IFERROR(1/(1/Table[Q2]),""))

confirmed with Control+Shift+Enter.