Formula for omitting zeros in an average

[jrpeel22]

Basically, I have 3 columns (A, X, Z) and I want it to average those without factoring in the zero.

The closest thing I could find towards performing this is the formula listed below:

=SUM(A1:A100)/COUNTIF(A1:A100,">0")

I altered the forumala to the following to suit my needs:

=SUM(A,X,Z)/COUNTIF(A,X,Z,">0")

The above formula did not work and futhermore I'm not even sure I have the right formula for what I am trying to accomplish.

Please help! Thanks!

 

[Jonmo1]

Welcome to the board.
Non contiguous ranges stink!!!

You'll have to do someting like
=SUM(A,X,Z)/(COUNTIF(A,">0")+COUNTIF(X,">0")+COUNTIF(Z,">0"))

I have a thought for something better, but it's still developing.....

 

[T. Valko]

Basically, I have 3 columns (A, X, Z) and I want it to average those without factoring in the zero.

The closest thing I could find towards performing this is the formula listed below:

=SUM(A1:A100)/COUNTIF(A1:A100,">0")

I altered the forumala to the following to suit my needs:

=SUM(A,X,Z)/COUNTIF(A,X,Z,">0")

The above formula did not work and futhermore I'm not even sure I have the right formula for what I am trying to accomplish.

Please help! Thanks!

Assuming no negative numbers involved...

Book1
	A	X	Z	AA
2	92	13	0	51
3	0	27	100	_
4	33	0	41	_
5	46	48	59	_
Sheet1

This formula entered in AA2:

=SUM(A2:A5,X2:X5,Z2:Z5)/INDEX(FREQUENCY((A2:A5,X2:X5,Z2:Z5),0),2)