Average formula with a difference.

[Michael D]

I have a woorksheet which contains values in rows of four.
I need to find the value for each row. Sounds easy,
here's the trick. Not all rows have rows have 4 values
thus i need a formula that ignores the zero values and
only takes into account cells with a value graeter than
zero. So if there are 3 values in the row the formula
divides by 3 etc etc.

Thanking you
Michael D

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(A11>0,A11))

 

[RagDyer]

Or, you could try this non-array formula:

=SUM(A1:A4)/COUNTIF(A1:A4,">0")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(A11>0,A11))

 

[Guest]

Thank you frank
Sorry about the double message
MD

-----Original Message-----
Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(A11>0,A11))

--
Regards
Frank Kabel
Frankfurt, Germany



.

 

[Harlan Grove]

Or, you could try this non-array formula:

=SUM(A1:A4)/COUNTIF(A1:A4,">0")

...

Unless there were negative values, in which case

=SUMIF(A1:A4,">0")/COUNTIF(A1:A4,">0")

 

[RagDyer]

Point to consider ... shouldn't negative values actually be included in the
average, as being considered part of an array?

=SUM(A1:A4)/COUNTIF(A1:A4,"<>0")

The problem being, the above doesn't work with a null (empty) cell!

Another point ... some solutions require zero to be included, while others
do not.

So, the OP makes the rules.
Problem is, they don't often include *all* stipulations in their requests.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Or, you could try this non-array formula:

=SUM(A1:A4)/COUNTIF(A1:A4,">0")

...

Unless there were negative values, in which case

=SUMIF(A1:A4,">0")/COUNTIF(A1:A4,">0")

 

[Earl Kiosterud]

And if you wanted the negative values included in the average, that is, you
wanted only 0 values excluded (perhaps because links to empty cells return
0), you'd use:

=SUM(A1:A4) / (COUNTIF(A1:A4,"<>0")-COUNTIF(A1:A4,""))

 

[Harlan Grove]

Point to consider ... shouldn't negative values actually be included in the
average, as being considered part of an array?

=SUM(A1:A4)/COUNTIF(A1:A4,"<>0")

The problem being, the above doesn't work with a null (empty) cell!

Another point ... some solutions require zero to be included, while others
do not.

So, the OP makes the rules.
Problem is, they don't often include *all* stipulations in their requests.

...

If both positives and negatives are valid values in a sample of observed values,
then unless the process producing those values weren't continuous zero would
also have to be a valid value. In other words, including negatives but excluding
zeros would tend to overstate the resulting averages.

As for the denominator, it needs to be

(COUNTIF(C5:C10,"<>0")-COUNTIF(C5:C10,"=")-COUNTIF(C5:C10,"*"))

 

[RagDyer]

To "sort of" change the subject slightly, aren't the arguments that you and
Earl are using for the denominator supposedly "unreliable" for all XL
versions except '03?

Back on the subject, I do agree with what you're saying, but I also repeat
.... the OP rules the roost as to stipulations, whether or not they
(stipulations) are technically OR matematically correct in the eyes of the
"experts".
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Point to consider ... shouldn't negative values actually be included in the
average, as being considered part of an array?

=SUM(A1:A4)/COUNTIF(A1:A4,"<>0")

The problem being, the above doesn't work with a null (empty) cell!

Another point ... some solutions require zero to be included, while others
do not.

So, the OP makes the rules.
Problem is, they don't often include *all* stipulations in their requests.

...

If both positives and negatives are valid values in a sample of observed
values,
then unless the process producing those values weren't continuous zero would
also have to be a valid value. In other words, including negatives but
excluding
zeros would tend to overstate the resulting averages.

As for the denominator, it needs to be

(COUNTIF(C5:C10,"<>0")-COUNTIF(C5:C10,"=")-COUNTIF(C5:C10,"*"))

 

[Harlan Grove]

To "sort of" change the subject slightly, aren't the arguments that
you and Earl are using for the denominator supposedly "unreliable"
for all XL versions except '03?

Earl's denominator term is

(COUNTIF(A1:A4,"<>0")-COUNTIF(A1:A4,""))

and mine is

(COUNTIF(C5:C10,"<>0")-COUNTIF(C5:C10,"=")-COUNTIF(C5:C10,"*"))

I only have XL97 and XL2K, so those are the only versions I can test. In
them, Earl's denominator term would include all cells containing text other
than "" in the count of cells evaluating nonzero. In my denominator term,
the COUNTIF(..,"=") term excludes blank cells and the COUNTIF(..,"*") term
excludes all text cells. However, the direct approach would be better.

Back on the subject, I do agree with what you're saying, but I also
repeat ... the OP rules the roost as to stipulations, whether or not
they (stipulations) are technically OR matematically correct in the
eyes of the "experts".

The OP can & will do whatever the OP wants to do, but that doesn't prohibit
respondents from offering advice and/or comments. Consider that the price of
otherwise free peer-to-peer support. I don't participate to tell OPs how to
perform given tasks, I participate to tell them how to do the right thing
well. If they don't want to do the right thing, that's their own affair, but
I haven't sinned by ommission.

 

[RagDyeR]

<<"I only have XL97 and XL2K, so those are the only versions I can test. ">>

Check out this old post of mine, and then the responses (comments) I
received.

http://tinyurl.com/3yyu4

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

To "sort of" change the subject slightly, aren't the arguments that
you and Earl are using for the denominator supposedly "unreliable"
for all XL versions except '03?

Earl's denominator term is

(COUNTIF(A1:A4,"<>0")-COUNTIF(A1:A4,""))

and mine is

(COUNTIF(C5:C10,"<>0")-COUNTIF(C5:C10,"=")-COUNTIF(C5:C10,"*"))

I only have XL97 and XL2K, so those are the only versions I can test. In
them, Earl's denominator term would include all cells containing text other
than "" in the count of cells evaluating nonzero. In my denominator term,
the COUNTIF(..,"=") term excludes blank cells and the COUNTIF(..,"*") term
excludes all text cells. However, the direct approach would be better.

Back on the subject, I do agree with what you're saying, but I also
repeat ... the OP rules the roost as to stipulations, whether or not
they (stipulations) are technically OR matematically correct in the
eyes of the "experts".

The OP can & will do whatever the OP wants to do, but that doesn't prohibit
respondents from offering advice and/or comments. Consider that the price of
otherwise free peer-to-peer support. I don't participate to tell OPs how to
perform given tasks, I participate to tell them how to do the right thing
well. If they don't want to do the right thing, that's their own affair, but
I haven't sinned by ommission.

 

[Harlan Grove]

...
...

Check out this old post of mine, and then the responses (comments) I
received.

http://tinyurl.com/3yyu4

...

You mean the fact that COUNTIF and SUMIF seem to operate on the intersection of
the ranges given as their 1st arguments and the used range of the worksheets
containing them. Yes, that can be a problem in earlier versions, but it can be
dealt with my forcing the used range to be as large as the references in the 1st
arg.

Also, in the thread above, the problem was with COUNTIF(RNG,"") not counting all
blank or "" cells. Since cells in RNG but not in its worksheet's used range
should be included in this COUNTIF formula's result (at least naively), this is
a problem. In the case of both Earl's and my denominator expressions, the common
COUNTIF(RNG,"<>0") doesn't include blank cells outside the used range, but those
shouldn't be included anyway since if they're outside the used range, there's
nothing in those cells, so perforce nothing to average. Also, neither
COUNTIF(RNG,""), COUNTIF(RNG,"=") nor COUNTIF(RNG,"*") would count any cells
outside the used range, so net of text other than "" in Earl's, both of our
denominator expressions would work no matter how much of RNG fell outside of
RNG's worksheet's used range. The problem to which you refer affects only
counting blank cells on their own, not removing a count of blank cells from a
different (so not blank cell count) COUNTIF result.

 

[RagDyer]

So I don't have to be so "gun shy" in using them, as long as I just give it
some thought before hand!

Thanks Harlan.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

...
...

Check out this old post of mine, and then the responses (comments) I
received.

http://tinyurl.com/3yyu4

...

You mean the fact that COUNTIF and SUMIF seem to operate on the intersection
of
the ranges given as their 1st arguments and the used range of the worksheets
containing them. Yes, that can be a problem in earlier versions, but it can
be
dealt with my forcing the used range to be as large as the references in the
1st
arg.

Also, in the thread above, the problem was with COUNTIF(RNG,"") not counting
all
blank or "" cells. Since cells in RNG but not in its worksheet's used range
should be included in this COUNTIF formula's result (at least naively), this
is
a problem. In the case of both Earl's and my denominator expressions, the
common
COUNTIF(RNG,"<>0") doesn't include blank cells outside the used range, but
those
shouldn't be included anyway since if they're outside the used range,
there's
nothing in those cells, so perforce nothing to average. Also, neither
COUNTIF(RNG,""), COUNTIF(RNG,"=") nor COUNTIF(RNG,"*") would count any cells
outside the used range, so net of text other than "" in Earl's, both of our
denominator expressions would work no matter how much of RNG fell outside of
RNG's worksheet's used range. The problem to which you refer affects only
counting blank cells on their own, not removing a count of blank cells from
a
different (so not blank cell count) COUNTIF result.