On Wed, 22 Nov 2000, Peter Jay Salzman wrote:
> concerning the spreadsheet...
>
> how do you take the average of a range of cells, without taking any 0's into
> account?
>
> so if
>
> A1 1
> A2 1
> A3 1
> A4 1
> A5 0
>
> AVERAGE(A1:A5) yields 1.0, not .8.
You got a problem, man? Equal Rights For Zeroes! Zero Is Not A Null Value!
One way is to set up a second column that tests each value "=IF(A1=0,0,1)"
and adds those up, you can divide that into SUM(A1:A5) to get your answer.
Most spreadsheets were developed and used by people who never heard of
the concept of a null value (not zero, drops out of AVERAGE
function) which is common in databases. Thus, a blank cell seems to count
as zero if it is empty.
I don't have StarOffice available at the moment, but Excel recently added
a "DAVERAGE" function that could be abused to do what you want, so SO
might have it too.
---------------------------------------------------------------------------
Jeff Newmiller The ..... ..... Go Live...
DCN:<[EMAIL PROTECTED]> Basics: ##.#. ##.#. Live Go...
Work:<[EMAIL PROTECTED]> Live: OO#.. Dead: OO#.. Playing
Research Engineer (Solar/Batteries O.O#. #.O#. with
/Software/Embedded Controllers) .OO#. .OO#. rocks...2k
---------------------------------------------------------------------------
