Average Non-Continuous Cells Without Zero's
 

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. :eek:

Any suggestions? Is it even possible?

Thanks


--
raeleanne
------------------------------------------------------------------------
raeleanne's Profile: http://www.excelforum.com/member.php...o&userid=25437
View this thread: http://www.excelforum.com/showthread...hreadid=388759

I do not understand why you cannot use COUNTIF()

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



raeleanne Wrote:
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. :eek:

Any suggestions? Is it even possible?

Thanks


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=388759

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),M AX(C84,0),MAX(H84,0))/SUM((C320),(H320),(C580),(H580),(C840),(H840 ))


Does that help?
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=388759

raeleanne,

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

HTH,
Bernie
MS Excel MVP


"raeleanne" wrote in message
...

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. :eek:

Any suggestions? Is it even possible?

Thanks


--
raeleanne
------------------------------------------------------------------------
raeleanne's Profile: http://www.excelforum.com/member.php...o&userid=25437
View this thread: http://www.excelforum.com/showthread...hreadid=388759

Hi!

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

=SUM(named_range)/SUMPRODUCT((MOD(ROW(32:84),26)=6)*(C32:C840)+(H32 :H840))

Biff

"raeleanne" wrote
in message ...

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. :eek:

Any suggestions? Is it even possible?

Thanks


--
raeleanne
------------------------------------------------------------------------
raeleanne's Profile:
http://www.excelforum.com/member.php...o&userid=25437
View this thread: http://www.excelforum.com/showthread...hreadid=388759

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

=SUM(MAX(C32,0),MAX(H32,0),MAX(C58,0),MAX(H58,0),M
AX(C84,0),MAX(H84,0))/SUM((C320),(H320),(C580),(H580),(C840),(H840 ))
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 the
best solutions.

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


--
raeleanne
------------------------------------------------------------------------
raeleanne's Profile: http://www.excelforum.com/member.php...o&userid=25437
View this thread: http://www.excelforum.com/showthread...hreadid=388759

Try...

=AVERAGE(IF(N(INDIRECT({"C32","C58","C84","H32","H 58","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!

raeleanne Wrote:
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. :eek:

Any suggestions? Is it even possible?

Thanks


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=388759

Nicely done, Domenic.

I think wrapping either of them in a MAX, MIN, or SUMPRODUCT function
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,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=388759

Ron Coderre Wrote:
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 as
you know is volatile. But the disadvantage is that the formula, as it
stands, doesn't allow for empty cells. In this case, the formula could
be changed as follows...

=AVERAGE(IF(SMALL((C32,C58,C84,H32,H58,H84),ROW(IN DIRECT("1:"&COUNT(C32,C58,C84,H32,H58,H84))))0,SM ALL((C32,C58,C84,H32,H58,H84),ROW(INDIRECT("1:"&CO UNT(C32,C58,C84,H32,H58,H84))))))

I think wrapping either of them in a MAX, MIN, or SUMPRODUCT function
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 MAX
and MIN functions and confirm the formula with CONTROL+SHIFT+ENTER.
But that's just my personal preference. :)


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=388759