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

 

[offthelip]

try this

=AVERAGEIF(C2:C20,"<>0",C2:C20)

 

[AhoyNC]

If the cells are really blank you could just use the AVERAGE function.

Book1
	A	B
1	Data
2	5
3	6
4
5	3
6	5
7
8	7	5.2
Sheet1

Cell Formulas
Range	Formula
B8	B8	=AVERAGE(A2:A8)

 

[Eric W]

Also check that your numbers are saved as numbers, and not text.

 

[msmith3]

If the cells are really blank you could just use the AVERAGE function.

Book1
	A	B
1	Data
2	5
3	6
4
5	3
6	5
7
8	7	5.2
Sheet1

Cell Formulas
Range	Formula
B8	B8	=AVERAGE(A2:A8)

I guess the cells aren't really blank because neither reply is working for it. The cells have a formula in them that is returning a blank value, does that change what the average formula should look like?

 

[Eric W]

What's the formula in those cells? If it's like

=IF(A1="Y","5","")

then that means it's returning a text version of the 5, which the AVERAGE or AVERAGEIF will ignore. Change it to:

=IF(A1="Y",5,"")

If that's not it, please show the formula you're using.

 

[msmith3]

What's the formula in those cells? If it's like

=IF(A1="Y","5","")

then that means it's returning a text version of the 5, which the AVERAGE or AVERAGEIF will ignore. Change it to:

=IF(A1="Y",5,"")

If that's not it, please show the formula you're using.

The formula shown in the bar is what I have in every cell for column Q1, I'm trying to get the average of Q1 in cell D8 which currently says "N/A".

 

[offthelip]

change the

LEFT(Y38,1)

to

VALUE(LEFT(Y38,1))

 

[Eric W]

LEFT is a text function, and will always return a text value. And Average ignores text values. There are a few ways around it. If you are sure that you're always going to get a number from the LEFT function, you can change that formula to:

=IF(ISBLANK(B38),"",LEFT(Y38,1)+0)

Performing a math operation on a number saved as text, will convert it to a number. But if it's not a number, you'll get an error.

 

[msmith3]

LEFT is a text function, and will always return a text value. And Average ignores text values. There are a few ways around it. If you are sure that you're always going to get a number from the LEFT function, you can change that formula to:

=IF(ISBLANK(B38),"",LEFT(Y38,1)+0)

Performing a math operation on a number saved as text, will convert it to a number. But if it's not a number, you'll get an error.

You're a life saver, thank you!!!