Average of 5 different cells (not a range), ignoring zeros.

[EddieD]

Hi everyone,
Im trying to find the average of a series of cells, however they are not all in sequential columns. I also want the formula to skip over cells with zero figures, not averaging those.

Here's what I had, without the zero function:

=AVERAGE(C189,O189,AA189,AM189,AY189)

Here's my attempt with skipping the zeros - which doesn't work.

=AVERAGEIF(C189,O189,AA189,AM189,AY189, "<>0")

Thanks for your help.

 

[JamesCanale]

MrExcelPlayground12.xlsx
	F	G	H	I	J	K	L	M
7	6	2		0		7		9
Sheet11

Cell Formulas
Range	Formula
F7	F7	=SUM(INDEX(G7:M7,1,{1,3,5,7}))/SUMPRODUCT(--(INDEX(G7:M7,1,{1,3,5,7})<>0))

 

[EddieD]

wow there's a formula. thanks for your help.

 

[shift-del]

Hi

Or:

=AVERAGE(FILTER(C189:AY189,(C189:AY189<>0)*{1.0.0.0.0.0.0.0.0.0.0.0.1.0.0.0.0.0.0.0.0.0.0.0.1.0.0.0.0.0.0.0.0.0.0.0.1.0.0.0.0.0.0.0.0.0.0.0.1}))

 

[EddieD]

Can you kindly help me understand the 1.0.0.0 ..... etc?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[shift-del]

Can you kindly help me understand the 1.0.0.0 ..... etc?

A 1 for every column that should be considered, count from column C to column AY.

 

[jasonb75]

Do you have column headers where there is a common identifier that could be used to pick out the columns to average?

If you do then you can do it with something based on

=AVERAGEIFS(C189:AY189,C189:AY189,"<>0",C$1:AY$1,"*header*")

Without suitable identifiers in the headers, maybe something like

=LET(a,INDEX(189:189,1,SEQUENCE(1,5,3,12)),AVERAGE(IF(a,a)))