software

I need to average the figures in several cells. However some cells have a 0
in them.

I therefore want the formula to ignore the cells which have a zero.

I have used the AVERAGE amp; AVERAGEA function, but both count 0 cells.
(although AVERAGEA ignores blank cells, I need to keep the 0s in as they are
linked to another formula)

Thanks.

Lorraine, here is one way,

=SUMPRODUCT((A1:A10)/COUNTIF(A1:A10,quot;lt;gt;0quot;))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 amp; 2003

quot;Lorrainequot; gt; wrote in message
...
gt; I need to average the figures in several cells. However some cells have a
0
gt; in them.
gt;
gt; I therefore want the formula to ignore the cells which have a zero.
gt;
gt; I have used the AVERAGE amp; AVERAGEA function, but both count 0 cells.
gt; (although AVERAGEA ignores blank cells, I need to keep the 0s in as they
are
gt; linked to another formula)
gt;
gt; Thanks.
On Thu, 22 Dec 2005 03:44:02 -0800, quot;Lorrainequot;
gt; wrote:

gt;I need to average the figures in several cells. However some cells have a 0
gt;in them.
gt;
gt;I therefore want the formula to ignore the cells which have a zero.
gt;
gt;I have used the AVERAGE amp; AVERAGEA function, but both count 0 cells.
gt;(although AVERAGEA ignores blank cells, I need to keep the 0s in as they are
gt;linked to another formula)
gt;
gt;Thanks.

Use this **array** formula:

=AVERAGE(IF(rnglt;gt;0,rng))

(substitute your range to average for 'rng').

To enter an **array** formula, hold down lt;ctrlgt;lt;shiftgt; while hitting lt;entergt;.
Excel will place braces {...} around the formula.
--ron

Thanks - this works, although Excel told me to put an extra bracket at the end.
You have saved me a lot of time.

Thanks again.

quot;Paul Bquot; wrote:

gt; Lorraine, here is one way,
gt;
gt; =SUMPRODUCT((A1:A10)/COUNTIF(A1:A10,quot;lt;gt;0quot;))
gt;
gt; --
gt; Paul B
gt; Always backup your data before trying something new
gt; Please post any response to the newsgroups so others can benefit from it
gt; Feedback on answers is always appreciated!
gt; Using Excel 2002 amp; 2003
gt;
gt; quot;Lorrainequot; gt; wrote in message
gt; ...
gt; gt; I need to average the figures in several cells. However some cells have a
gt; 0
gt; gt; in them.
gt; gt;
gt; gt; I therefore want the formula to ignore the cells which have a zero.
gt; gt;
gt; gt; I have used the AVERAGE amp; AVERAGEA function, but both count 0 cells.
gt; gt; (although AVERAGEA ignores blank cells, I need to keep the 0s in as they
gt; are
gt; gt; linked to another formula)
gt; gt;
gt; gt; Thanks.
gt;
gt;
gt;

Thanks for your help- this also works. Very Clever !

quot;Ron Rosenfeldquot; wrote:

gt; On Thu, 22 Dec 2005 03:44:02 -0800, quot;Lorrainequot;
gt; gt; wrote:
gt;
gt; gt;I need to average the figures in several cells. However some cells have a 0
gt; gt;in them.
gt; gt;
gt; gt;I therefore want the formula to ignore the cells which have a zero.
gt; gt;
gt; gt;I have used the AVERAGE amp; AVERAGEA function, but both count 0 cells.
gt; gt;(although AVERAGEA ignores blank cells, I need to keep the 0s in as they are
gt; gt;linked to another formula)
gt; gt;
gt; gt;Thanks.
gt;
gt; Use this **array** formula:
gt;
gt; =AVERAGE(IF(rnglt;gt;0,rng))
gt;
gt; (substitute your range to average for 'rng').
gt;
gt; To enter an **array** formula, hold down lt;ctrlgt;lt;shiftgt; while hitting lt;entergt;.
gt; Excel will place braces {...} around the formula.
gt; --ron
gt;

Would this formula change if instead of a range of figures, several figures
from different parts of the spreadsheet were used i.e d15 f18 g10.

Thanks.

quot;Paul Bquot; wrote:

gt; Lorraine, here is one way,
gt;
gt; =SUMPRODUCT((A1:A10)/COUNTIF(A1:A10,quot;lt;gt;0quot;))
gt;
gt; --
gt; Paul B
gt; Always backup your data before trying something new
gt; Please post any response to the newsgroups so others can benefit from it
gt; Feedback on answers is always appreciated!
gt; Using Excel 2002 amp; 2003
gt;
gt; quot;Lorrainequot; gt; wrote in message
gt; ...
gt; gt; I need to average the figures in several cells. However some cells have a
gt; 0
gt; gt; in them.
gt; gt;
gt; gt; I therefore want the formula to ignore the cells which have a zero.
gt; gt;
gt; gt; I have used the AVERAGE amp; AVERAGEA function, but both count 0 cells.
gt; gt; (although AVERAGEA ignores blank cells, I need to keep the 0s in as they
gt; are
gt; gt; linked to another formula)
gt; gt;
gt; gt; Thanks.
gt;
gt;
gt;

I don't think that is straightforward at all. This does it but requires some
explanation

=SUMPRODUCT(--(N(OFFSET(D10:G18,{5,8,0},{0,2,3},1,1))lt;gt;0),--(N(OFFSET(D10:G1
8,{5,8,0},{0,2,3},1,1))))/SUMPRODUCT(--(N(OFFSET(D10:G18,{5,8,0},{0,2,3},1,1
))lt;gt;0))

D10:G18 is the smallest range that encompasses all 3 cells.

To get D15, F18 and G10 you need row and column offsets

D15 - rrow offset is 5 (15-10), column offset is 0 (D-D)
F18 - rrow offset is 8 (18-10), column offset is 2 (F-D)
G10 - rrow offset is 0 (10-10), column offset is 3 (G-D0

so hopefully you can see why we use the arrays {5,8,0} and {0,2,3}

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Lorrainequot; gt; wrote in message
...
gt; Would this formula change if instead of a range of figures, several
figures
gt; from different parts of the spreadsheet were used i.e d15 f18 g10.
gt;
gt; Thanks.
gt;
gt; quot;Paul Bquot; wrote:
gt;
gt; gt; Lorraine, here is one way,
gt; gt;
gt; gt; =SUMPRODUCT((A1:A10)/COUNTIF(A1:A10,quot;lt;gt;0quot;))
gt; gt;
gt; gt; --
gt; gt; Paul B
gt; gt; Always backup your data before trying something new
gt; gt; Please post any response to the newsgroups so others can benefit from it
gt; gt; Feedback on answers is always appreciated!
gt; gt; Using Excel 2002 amp; 2003
gt; gt;
gt; gt; quot;Lorrainequot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I need to average the figures in several cells. However some cells
have a
gt; gt; 0
gt; gt; gt; in them.
gt; gt; gt;
gt; gt; gt; I therefore want the formula to ignore the cells which have a zero.
gt; gt; gt;
gt; gt; gt; I have used the AVERAGE amp; AVERAGEA function, but both count 0 cells.
gt; gt; gt; (although AVERAGEA ignores blank cells, I need to keep the 0s in as
they
gt; gt; are
gt; gt; gt; linked to another formula)
gt; gt; gt;
gt; gt; gt; Thanks.
gt; gt;
gt; gt;
gt; gt;
Actually, seeing this, we can use the same principle I gave before, but
provide a much neater solution

=AVERAGE(IF(N(OFFSET(D10:G18,{5,8,0},{0,2,3},1,1)) lt;gt;0,N(OFFSET(D10:G18,{5,8,
0},{0,2,3},1,1))))

still an array formula, same rationale with the embedded constants arrays.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Lorrainequot; gt; wrote in message
...
gt; Thanks for your help- this also works. Very Clever !
gt;
gt; quot;Ron Rosenfeldquot; wrote:
gt;
gt; gt; On Thu, 22 Dec 2005 03:44:02 -0800, quot;Lorrainequot;
gt; gt; gt; wrote:
gt; gt;
gt; gt; gt;I need to average the figures in several cells. However some cells have
a 0
gt; gt; gt;in them.
gt; gt; gt;
gt; gt; gt;I therefore want the formula to ignore the cells which have a zero.
gt; gt; gt;
gt; gt; gt;I have used the AVERAGE amp; AVERAGEA function, but both count 0 cells.
gt; gt; gt;(although AVERAGEA ignores blank cells, I need to keep the 0s in as
they are
gt; gt; gt;linked to another formula)
gt; gt; gt;
gt; gt; gt;Thanks.
gt; gt;
gt; gt; Use this **array** formula:
gt; gt;
gt; gt; =AVERAGE(IF(rnglt;gt;0,rng))
gt; gt;
gt; gt; (substitute your range to average for 'rng').
gt; gt;
gt; gt; To enter an **array** formula, hold down lt;ctrlgt;lt;shiftgt; while hitting
lt;entergt;.
gt; gt; Excel will place braces {...} around the formula.
gt; gt; --ron
gt; gt;

Hi,Another option..

Try,

=SUMPRODUCT(SUMIF(INDIRECT({quot;D15quot;,quot;F18quot;,quot;G10quot;}),quot;lt; gt;0quot;,INDIRECT({quot;D15quot;,quot;F18quot;,quot;G10quot;})))/SUMPRODUCT(COUNTIF(INDIRECT({quot;D15quot;,quot;F18quot;,quot;G10quot;}),quot; lt;gt;0quot;))

Normal enter.

HTH--
Krishnakumar------------------------------------------------------------------------
Krishnakumar's Profile: www.excelforum.com/member.php...oamp;userid=20138
View this thread: www.excelforum.com/showthread...hreadid=495468Note that your formula does not work if any of the cells are blank, another
way would be

=SUM(D15,F18,G10)/SUMPRODUCT(N(LARGE((D15,F18,G10),ROW(INDIRECT(quot;1:quot; amp;COUNT(D
15,F18,G10))))lt;gt;0))

entered normally

--

Regards,

Peo Sjoblom

quot;Krishnakumarquot; gt;
wrote in message
news:Krishnakumar.20heay_1135321881.0492@excelforu m-nospam.com...
gt;
gt; Hi,
gt;
gt;
gt; Another option..
gt;
gt; Try,
gt;
gt;
=SUMPRODUCT(SUMIF(INDIRECT({quot;D15quot;,quot;F18quot;,quot;G10quot;}),quot;lt; gt;0quot;,INDIRECT({quot;D15quot;,quot;F18quot;,
quot;G10quot;})))/SUMPRODUCT(COUNTIF(INDIRECT({quot;D15quot;,quot;F18quot;,quot;G10quot;}),quot; lt;gt;0quot;))
gt;
gt; Normal enter.
gt;
gt; HTH
gt;
gt;
gt; --
gt; Krishnakumar
gt;
gt;
gt; ------------------------------------------------------------------------
gt; Krishnakumar's Profile:
www.excelforum.com/member.php...oamp;userid=20138
gt; View this thread: www.excelforum.com/showthread...hreadid=495468
gt;