Re: Average formula with a difference.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Harlan Grove (hrlngrv_at_aol.com)
Date: 06/15/04 

Date: Tue, 15 Jun 2004 23:00:40 GMT

"RagDyer" wrote...
>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,"*")) 

--
To top-post is human, to bottom-post and snip is sublime.

Relevant Pages

  • Re: Arrays of zero length
    ... The minor strangeness is that when you allocate an array to zero size, ... saying something like that allocating an array makes it undefined ... In f77, zero-size was disallowed, ...
    (comp.lang.fortran)
  • Re: should every thing be zero indexed?
    ... Other families of languages count from 1. ... its number is zero: I always start counting at zero". ... > as a fundamental concept in both the definition of array indices and ... > for I in foo'range loop ...
    (comp.programming)
  • Re: Subset Sum problem (w/ limited scope)
    ... The "Subset Sum" problem (stated various ways depending on what you ... a subset of those integers that sums up to zero. ... Are there going to be duplicate values in your original array? ...
    (comp.lang.fortran)
  • Re: Subset Sum problem (w/ limited scope)
    ... The "Subset Sum" problem (stated various ways depending on what you ... a subset of those integers that sums up to zero. ... Are there going to be duplicate values in your original array? ...
    (comp.lang.fortran)
  • Re: addEvent - The late entry :)
    ... take any number of arguments and append them to an array. ... argument, which is fine on everything but Firefox, where it makes the ... 20 arguments Windows Safari 3 executes that expression in 48% of the ... and at zero arguments 47%. ...
    (comp.lang.javascript)