Average cells >0

[Tikey]

Hi

I wish to average a number of cells, but only if all the cells in the range are >0. I have tried using =AVERAGEIF(A1:A5 ,">0"), but this includes any odd cells that are >0 and in the first example below, gives an answer of 3 when it should be 0. The second example shows the result should only be when all values are > 0.

	A		A
1​	0​	1​	2​
2​	0​	2​	2​
3​	0​	3​	2​
4​	0​	4​	2​
5​	3​	5​	2​
Answer Required	0​		2​

​

Maybe this should be easy, but I cannot find a solution, any help appreciated.

 

[etaf]

perhaps
= IF ( Countif(A1:A5, 0)>0, "" , Average(A1:A5) )

 

[jmacleary]

Hi there. Does this do the job? NB The formulas are array formula so need entering with ctrl-shift-enter.

Book3 (version 1).xlsb
	A	B
1	1	0
2	2	0
3	3	0
4	4	0
5	5	3
6	3	0
Sheet3

Cell Formulas
Range	Formula
A6:B6	A6	=IF(A1:A5>0,AVERAGE(A1:A5),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Tikey]

etaf

Thanks for your reply, this works but does not display the 0 in the answer as in my example 1. Maybe I'm doing something wrong.

 

[Tikey]

jmacleary

Thanks for your reply, unfortunately this appears to still provide an average when one of the cells is 0.

 

[JimM]

Not wishing to steal etaf's thunder but just swap out the "" with 0

ie
= IF ( Countif(A1:A5, 0)>0, 0 , Average(A1:A5) )

 

[jmacleary]

jmacleary

Thanks for your reply, unfortunately this appears to still provide an average when one of the cells is 0.

Yes, you're right - I didn't test it properly. You can use etaf's answer slightly modified like:
=IF(COUNTIF(A1:A5,0)>0,0,AVERAGE(A1:A5))
to get the zeroes you want.

 

[Tikey]

Everyone, thanks all your replies, problem sorted, much appreciated.