Average Non-Continuous Cells Without Zero's

[raeleanne]

One more problem and this spreadsheet should be done...

I need to average the results in a number of cells
(C32+H32+C58+H58+C84+H84) without including cells that have a value of
0.0%

I have tried:
=AVERAGE(IF((C32+H32+C58+H58+C84+H84)>=0,(C32+H32+C58+H58+C84+H84)))
but this just seems to Add everything together

I can't sum the cells and then divide by a COUNTIF becuase it won't let
me select anything other than a range.

Any suggestions? Is it even possible?

Thanks

 

[Morrigan]

I do not understand why you cannot use COUNTIF()

=SUM(A:A)/COUNTIF(A:A,">0")

 

[Ron Coderre]

You can use this approach when the list of individual cells is not
long:

=SUM(MAX(C32,0),MAX(H32,0),MAX(C58,0),MAX(H58,0),MAX(C84,0),MAX(H84,0))/SUM((C32>0),(H32>0),(C58>0),(H58>0),(C84>0),(H84>0))


Does that help?
Ron

 

[Bernie Deitrick]

raeleanne,

=(C32+H32+C58+H58+C84+H84)/SUM(IF(C32<>0,1,0),IF(H32<>0,1,0),......)

HTH,
Bernie
MS Excel MVP

 

[Biff]

Hi!

Create a named range for C32,H32,C58,H58,C84,H84.

=SUM(named_range)/SUMPRODUCT((MOD(ROW(32:84),26)=6)*(C32:C84>0)+(H32:H84>0))

Biff

 

[raeleanne]

You can't use COUNTIF on cells that are not in a range and I was no
sure how to make noncontinuous cells into a range.

=SUM(MAX(C32,0),MAX(H32,0),MAX(C58,0),MAX(H58,0),
AX(C84,0),MAX(H84,0))/SUM((C32>0),(H32>0),(C58>0),(H58>0),(C84>0),(H84>0)
gives me an error of #NAME?

=(C32+H32+C58+H58+C84+H84)/SUM(IF(C32<>0,1,0),IF(H32<>0,1,0),......
works.

Thank you so much. It is not easy to do but I think it is one of th
best solutions.

Thanks again for all of your help. The Spreadsheet is done!!!! yea
:

 

[Domenic]

Try...

=AVERAGE(IF(N(INDIRECT({"C32","C58","C84","H32","H58","H84}))>0,N(INDIRECT({"C32","C58","C84","H32","H58","H84}))))

or

=AVERAGE(IF(SMALL((C32,C58,C84,H32,H58,H84),{1,2,3,4,5,6})>0,SMALL((C32,C58,C84,H32,H58,H84),{1,2,3,4,5,6})))

Both these formulas need to be confired with CONTROL+SHIFT+ENTER, not
just ENTER.

Hope this helps!

 

[Ron Coderre]

Nicely done, Domenic.

I think wrapping either of them in a MAX, MIN, or SUMPRODUCT functio
will eliminate the need for CSE:

=MAX(AVERAGE(IF(N(INDIRECT({"C32","C58","C84","H32","H58","H84"}))>0,N(INDIRECT({"C32","C58","C84","H32","H58","H84"})))))

=MIN(AVERAGE(IF(SMALL((C32,C58,C84,H32,H58,H84),{1,2,3,4,5,6})>0,SMALL((C32,C58,C84,H32,H58,H84),{1,2,3,4,5,6}))))

Regards,
Ro

 

[Domenic]

Nicely done, Domenic.

Thanks Ron! I came across the second formula a couple of days ago.
It's definitely an interesting way to deal with non-contiguous cells.
The advantage is that it doesn't use the INDIRECT function, which a
you know is volatile. But the disadvantage is that the formula, as i
stands, doesn't allow for empty cells. In this case, the formula coul
be changed as follows...

=AVERAGE(IF(SMALL((C32,C58,C84,H32,H58,H84),ROW(INDIRECT("1:"&COUNT(C32,C58,C84,H32,H58,H84))))>0,SMALL((C32,C58,C84,H32,H58,H84),ROW(INDIRECT("1:"&COUNT(C32,C58,C84,H32,H58,H84))))))

I think wrapping either of them in a MAX, MIN, or SUMPRODUCT functio
will eliminate the need for CSE:

=MAX(AVERAGE(IF(N(INDIRECT({"C32","C58","C84","H32","H58","H84"}))>0,N(INDIRECT({"C32","C58","C84","H32","H58","H84"})))))

=MIN(AVERAGE(IF(SMALL((C32,C58,C84,H32,H58,H84),{1,2,3,4,5,6})>0,SMALL((C32,C58,C84,H32,H58,H84),{1,2,3,4,5,6}))))

While that would certainly work, my preference is to exclude the MA
and MIN functions and confirm the formula with CONTROL+SHIFT+ENTER.
But that's just my personal preference. :