functions-using average across columns

[kwg]

Need to use the average function and apply to only cells with a value greater than 0 across a range of 13 cells.

Example: 3 of the 13 cells have values over 0 but the "average" function in excel applies the calculation to the entire range with includes all the zeros. I only want to apply the average to cells with value > 0. I can visually count the cells and divide but quite a headache with over 1000 lines of data entries.

 

[Aladin Akyurek]

=AVERAGE(IF(Range,Range))

which must be confirmed with control+shift+enter instead of just enter.

 

[fairwinds]

Hi,

Try:

=AVERAGE(IF(A1:M1>0,A1:M1))

That you need to enter with Ctrl + shift + enter

 

[al_b_cnu]

Hi,

how about

=SUM(range)/COUNTIF(range,">0")

HTH

Alan

 

[onlyadrafter]

Hello,

I know there must be a better way, but here is one option

=SUMIF(A1:F1,">0",A1:F1)/COUNTIF(A1:F1,">0")

Change the cell refs as required,

Is this any use?

 

[LTunnicliffe]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	1	2	3	0	0	4	5	0	6	2.333333	3.5
Sheet1