How to AVERAGEIF with positive, negative and zeroes?

**innatedoc** · 10-20-2012, 07:08 PM

I have a column of data that includes positive numbers, negative numbers and zeroes. I tried =AVERAGEIF(AO2:AO52,"<>0") and =AVERAGEIF(AO2:AO52,"<>0",AO2:AO52)but I get a divide by zero error. If I do =AVERAGEIF(AO2:AO52,">0") I get an average of the positive numbers, and if I do =AVERAGEIF(AO2:AO52,"<0") I get an average of all the negative numbers, but I cant seem to get an average of ALL of the numbers (excluding the zeroes). Does anyone have an idea how to make this work in Excel 2007? Thanks!

**FDibbins** · 10-20-2012, 07:33 PM

i just tried what i think you want, and it worked fine. i entered this
1
2
3
4
1
0
1
4
0
7
4
-1
-2
-3
-6
=AVERAGEIF(A1:A15,">0") =3
=AVERAGEIF(A1:A15,"<0") = -3
=AVERAGEIF(A1:A15,"<>0") = 1.15384615384615

maybe upload a sample workbook for me to take a look at?

**Richard Buttrey** · 10-20-2012, 07:34 PM

Hi

Formula:

Please Login or Register  to view this content.

seems to work just fine.

**innatedoc** · 10-20-2012, 07:47 PM

Thank you. I fingered it out - I had divided by zero errors in the column I was attempting to average. I changed the formula in those columns to show a blank result instead of a divide by zero error and now the AVERAGEIF(AO2:AO52,"<>0") command works just fine.

**FDibbins** · 10-20-2012, 08:27 PM

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)