Averageif formula

[Blair]

I have a row of numbers, both positive and negative. I want to average the positive numbers and separately average the negative numbers. This works fine until I come to a row of numbers where none are positive and I get a #DIV/0! error message because none of the numbers in the range meet the positive criteria. Is there a way to return a blank cell or a zero instead of an error message? Any other work-around that you can think of?


Thanks

Blair

 

[Faseeh]

Hi Blair,


For following type of data use this formula:

[pre]

2
2
3
-4
5
-6
-8[/pre]
Enter... =SUMIF(H13:H19,">=0")/COUNTIF(H13:H19,">=0")

Adjust range according to your data set!!


Regards,

Faseeh

 

[Blair]

Thanks Faseeh: Guess I didn't state my problem clearly enough. I have a several rows containing a combination of positive and negative numbers. Each row terminates with two columns: one column contains the average of the positive numbers and the next column contains the average of the negative numbers. Everything works as it should until I have a row of all negative numbers and the error message shows itself in the column for positive numbers. When I have a row of all positive numbers then the error shows itself in the column for negative numbers. My goal is to have either a zero or a blank cell instead of the error message.

 

[mercatog]

Excel 2007 and later =IFERROR(AVERAGEIF(A2:G2,">0"),"")

 

[Blair]

Thank you mercatog. It works like a "champ".