Average Formula Using Sum/Countif - But Cells are nonadjacent

[jjm3066]

I am trying to do an average formula, where I need to eliminate any cells that hold a zero, so I am doing =sum(data range)/countif(data range,">0"). Easy formula... but the problem is that my range holds columns that are non-adjacent. They skip every other column where my data holds.

How do I allow the formula to work?

 

[T. Valko]

I am trying to do an average formula, where I need to eliminate any cells that hold a zero, so I am doing =sum(data range)/countif(data range,">0"). Easy formula... but the problem is that my range holds columns that are non-adjacent. They skip every other column where my data holds.

How do I allow the formula to work?

Maybe this...

Book1
	B	C	D	E	F	G	H
1	50	48	0	43	24	80	0
Sheet1

This array formula** will average every other cell starting from cell B1 and exclude 0 and negative values.

=AVERAGE(IF(MOD(COLUMN(B1:H1)-COLUMN(B1),2)=0,IF(B1:H1>0,B1:H1)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[jjm3066]

Ok thank you, it worked.

Now what happens if the columns are NOT actually every other column. Can I tell it to only pick up the column headers that contain a certain value and then provide an average of those columns.

 

[Aladin Akyurek]

Ok thank you, it worked.

Now what happens if the columns are NOT actually every other column. Can I tell it to only pick up the column headers that contain a certain value and then provide an average of those columns.

In case of non-contiguous cells, try...

=SUM(A4,E4,G4,L4)/INDEX(FREQUENCY((A4,E4,G4,L4),0),2)

Adjust the target cells to suit.

 

[jjm3066]

But all my column headers contain the same verbage.. it would be easier, I would have to select like 150 columns your way. Anything else?

 

[T. Valko]

Ok thank you, it worked.

Now what happens if the columns are NOT actually every other column. Can I tell it to only pick up the column headers that contain a certain value and then provide an average of those columns.

What version of Excel are you using?

 

[jjm3066]

07

 

[dcamos]

=average(if(and(columnheadersrange="text"),(data>0)),data,"")

CTRL+Shift+Enter

 

[T. Valko]

07

Try something like this...

Book1
	B	C	D	E	F	G	H	I	J
1	This	That	Them	This	This	That	This	Them	This
2	54	89	79	0	88	58	0	8	26
Sheet1

Get the average of row 2 if row 1 = "This" and row 2 is greater than 0:

=AVERAGEIFS(B2:J2,B1:J1,"this",B2:J2,">0")

 

[T. Valko]

=average(if(and(columnheadersrange="text"),(data>0)),data,"")

CTRL+Shift+Enter

You can't use AND to test an array in this application.

AND returns a single result where you need an array of results.