Question: Did I use averageif wrong

[ay123]

Hi, so I used the formula =AVERAGEIF(L2:L1000, ">10", G2:G1000) to calculate the average of my data, however, excel shows an error saying "AVERAGEIF caused a divide by zero error". But, when i tried =AVERAGEIF(L2:L1000, "11", G2:G1000) it did work. Does anyone know what went wrong? I'm kind of confused. Thanks!

 

[AhoyNC]

Could it be that your range L21:L1000 are text and not numeric?

 

[ay123]

I don't think so, both L and G columns are numeric not text

 

[RoryA]

Just to check - you are trying to average column G based on the values in column L, and not the other way round?

 

[ay123]

Yep, that's exactly what I'm trying to do

 

[AhoyNC]

The reason I'm thinking your column L is not numeric is your first formula with ">10" fails.
I would test a value in your column L with the ISNUMBER function and see what it returns.

 

[RoryA]

Do you have any error values in column G?

 

[ay123]

Ok, I tried the isnumber function and it returned with false. Perhaps because column L are also all functions? But I'm not sure since column L's functions all return with numbers.

I'm also confused why =AVERAGEIF(L2:L1000, "11", G2:G1000) will work, but =AVERAGEIF(L2:L1000, ">10", G2:G1000) doesn't work.

 

[ay123]

Nope- there aren't any error values in col L or G

 

[AhoyNC]

What is the function you are using in column L? The function is returning text, so ">10" will return 0 items and you get the the #DIV/0! error.
The "11" will work since you have some 11's in column L (in this case the AVERAGEIF function doesn't seem to care if the 11's are text or numeric). Sometimes you can get a formula to return a number by adding 0 to the formula. Try adding a +0 to the end of your formulas in column L.