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.

Any suggestions? Is it even possible?

Thanks

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

raeleanne,

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

HTH,
Bernie
MS Excel MVP


"raeleanne" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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:C84>0)+(H32:H84>0))

Biff

"raeleanne" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> 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((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 the best solutions.

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

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!

Originally Posted by 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

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

Originally Posted by Ron Coderre

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(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 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.

raeleanne,

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

HTH,
Bernie
MS Excel MVP


"raeleanne" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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:C84>0)+(H32:H84>0))

Biff

"raeleanne" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> raeleanne
> ------------------------------------------------------------------------
> raeleanne's Profile:
> http://www.excelforum.com/member.php...o&userid=25437
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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:C84>0)+(H32:H84>0))

Biff

"raeleanne" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> 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:C84>0)+(H32:H84>0))

Biff

"raeleanne" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> raeleanne
> ------------------------------------------------------------------------
> raeleanne's Profile:
> http://www.excelforum.com/member.php...o&userid=25437
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> raeleanne
> ------------------------------------------------------------------------
> raeleanne's Profile: http://www.excelforum.com/member.php...o&userid=25437
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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:C84>0)+(H32:H84>0))

Biff

"raeleanne" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> 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:C84>0)+(H32:H84>0))

Biff

"raeleanne" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> raeleanne
> ------------------------------------------------------------------------
> raeleanne's Profile:
> http://www.excelforum.com/member.php...o&userid=25437
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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:C84>0)+(H32:H84>0))

Biff

"raeleanne" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> raeleanne
> ------------------------------------------------------------------------
> raeleanne's Profile:
> http://www.excelforum.com/member.php...o&userid=25437
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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:C84>0)+(H32:H84>0))

Biff

"raeleanne" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> raeleanne
> ------------------------------------------------------------------------
> raeleanne's Profile:
> http://www.excelforum.com/member.php...o&userid=25437
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> raeleanne
> ------------------------------------------------------------------------
> raeleanne's Profile: http://www.excelforum.com/member.php...o&userid=25437
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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:C84>0)+(H32:H84>0))

Biff

"raeleanne" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> 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:C84>0)+(H32:H84>0))

Biff

"raeleanne" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> raeleanne
> ------------------------------------------------------------------------
> raeleanne's Profile:
> http://www.excelforum.com/member.php...o&userid=25437
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> raeleanne
> ------------------------------------------------------------------------
> raeleanne's Profile: http://www.excelforum.com/member.php...o&userid=25437
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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:C84>0)+(H32:H84>0))

Biff

"raeleanne" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> raeleanne
> ------------------------------------------------------------------------
> raeleanne's Profile:
> http://www.excelforum.com/member.php...o&userid=25437
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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:C84>0)+(H32:H84>0))

Biff

"raeleanne" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> raeleanne
> ------------------------------------------------------------------------
> raeleanne's Profile:
> http://www.excelforum.com/member.php...o&userid=25437
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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:C84>0)+(H32:H84>0))

Biff

"raeleanne" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> raeleanne
> ------------------------------------------------------------------------
> raeleanne's Profile:
> http://www.excelforum.com/member.php...o&userid=25437
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> 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:C84>0)+(H32:H84>0))

Biff

"raeleanne" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> raeleanne
> ------------------------------------------------------------------------
> raeleanne's Profile:
> http://www.excelforum.com/member.php...o&userid=25437
> View this thread: http://www.excelforum.com/showthread...hreadid=388759
>