Average a set of figures which ignores 0 entries
 

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

Average a set of figures which ignores 0 entries
 

Lorraine, here is one way,

=SUMPRODUCT((A1:A10)/COUNTIF(A1:A10,"<0"))

--
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 & 2003

"Lorraine" wrote in message
...
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 & 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.

Average a set of figures which ignores 0 entries
 

On Thu, 22 Dec 2005 03:44:02 -0800, "Lorraine"
wrote:

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

Use this **array** formula:

=AVERAGE(IF(rng<0,rng))

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

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.
--ron

Average a set of figures which ignores 0 entries
 

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.

"Paul B" wrote:

Lorraine, here is one way,

=SUMPRODUCT((A1:A10)/COUNTIF(A1:A10,"<0"))

--
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 & 2003

"Lorraine" wrote in message
...
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 & 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.

Average a set of figures which ignores 0 entries
 

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

"Ron Rosenfeld" wrote:

On Thu, 22 Dec 2005 03:44:02 -0800, "Lorraine"
wrote:

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

Use this **array** formula:

=AVERAGE(IF(rng<0,rng))

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

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.
--ron

Average a set of figures which ignores 0 entries
 

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.

"Paul B" wrote:

Lorraine, here is one way,

=SUMPRODUCT((A1:A10)/COUNTIF(A1:A10,"<0"))

--
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 & 2003

"Lorraine" wrote in message
...
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 & 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.

Average a set of figures which ignores 0 entries
 

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))<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
))<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)

"Lorraine" wrote in message
...
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.

"Paul B" wrote:

Lorraine, here is one way,

=SUMPRODUCT((A1:A10)/COUNTIF(A1:A10,"<0"))

--
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 & 2003

"Lorraine" wrote in message
...
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 & 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.

Average a set of figures which ignores 0 entries
 

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)) <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)

"Lorraine" wrote in message
...
Thanks for your help- this also works. Very Clever !

"Ron Rosenfeld" wrote:

On Thu, 22 Dec 2005 03:44:02 -0800, "Lorraine"
wrote:

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

Use this **array** formula:

=AVERAGE(IF(rng<0,rng))

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

To enter an **array** formula, hold down <ctrl<shift while hitting
<enter.
Excel will place braces {...} around the formula.
--ron

Average a set of figures which ignores 0 entries
 

Hi,


Another option..

Try,

=SUMPRODUCT(SUMIF(INDIRECT({"D15","F18","G10"}),"< 0",INDIRECT({"D15","F18","G10"})))/SUMPRODUCT(COUNTIF(INDIRECT({"D15","F18","G10"})," <0"))

Normal enter.

HTH


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=495468

Average a set of figures which ignores 0 entries
 

Note 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("1:" &COUNT(D
15,F18,G10))))<0))

entered normally

--

Regards,

Peo Sjoblom

"Krishnakumar"
wrote in message
news:Krishnakumar.20heay_1135321881.0492@excelforu m-nospam.com...

Hi,


Another option..

Try,


=SUMPRODUCT(SUMIF(INDIRECT({"D15","F18","G10"}),"< 0",INDIRECT({"D15","F18",
"G10"})))/SUMPRODUCT(COUNTIF(INDIRECT({"D15","F18","G10"})," <0"))

Normal enter.

HTH


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile:
http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=495468