average without zeros

[Fyr]

I'm using this formula:
=AVERAGE(C4,E4,G4,I4,K4,M4)

It a cell contains zero's, it does not average it right.
How can I use the above code to ignore zeros?

 

[Aladin Akyurek]

I'm using this formula:
=AVERAGE(C4,E4,G4,I4,K4,M4)

It a cell contains zero's, it does not average it right.
How can I use the above code to ignore zeros?

If the relevant cells are supposed to house numbers >= 0, then:

=SUM(C4,E4,G4,I4,K4,M4)/INDEX(FREQUENCY((C4,E4,G4,I4,K4,M4),{0}),2)

 

[Fyr]

thank you!!
that solved my problem!

 

[stanleydgromjr]

Fyr,

Aladin's formula is shorter.

But, this also works:
=SUM(SUMIF(INDIRECT({"C4","E4","G4","I4","K4","M4"}),">0"))/INDEX(FREQUENCY((C4,E4,G4,I4,K4,M4),0),2)


Have a great day,
Stan

 

[ZVI]

ADVERTISEMENT

For positive/negative excluding zero/empty/error values, array formula:
{=AVERAGE(IF((MOD(COLUMN(C4:M4)-COLUMN(C4),2)=0)*ISNUMBER(1/C4:M4),C4:M4))}

 

[ZVI]

For positive/negative excluding zero/empty/error values, array formula:
{=AVERAGE(IF((MOD(COLUMN(C4:M4)-COLUMN(C4),2)=0)*ISNUMBER(1/C4:M4),C4:M4))}

The same, but a little bit simpler (array formula):
{=AVERAGE(IF({1,0,1,0,1,0,1,0,1,0,1}*ISNUMBER(1/C4:M4),C4:M4))}

 

[Aladin Akyurek]

For positive/negative excluding zero/empty/error values, array formula:
{=AVERAGE(IF((MOD(COLUMN(C4:M4)-COLUMN(C4),2)=0)*ISNUMBER(1/C4:M4),C4:M4))}

Control+shift+enter:

=AVERAGE(
  IF(MOD(COLUMN(C4:M4)-COLUMN(C4),2)=0,
  IF(ISNUMBER(C4:M4),
  IF(C4:M4 > 0,
   C4:M4))))

which is an expensive piece formula for a set of 6 cells.

BTW, the matchematicians dislike the idea of exluding 0's if the data set is something like:

3,-3,0,2,1

although either an average of figures > 0 or < 0 is readily accepted.