Excluding zeros in functions

I have a spreadsheet with a few arrays in each of which there are a few
zeros. I have used a number fuctions (median stdev, var, average etc)
and now realize that I should have excluded the zeros. Is there any
quick way to do this?
Thanks
Daniel

Suppose your data is in A1:A4, and you want to find the average, then you must have used something like this.
=AVERAGE(A1:A4)

Instead, use:
=AVERAGE(IF(A1:A4=0,"",A1:A4))
and press control+shift+enter

Do this for all your formulae. While calculating, the 0s are replaced with blanks, and average ignores blanks.

- Mangesh

Daniel,

Test the range for zeroes within the function, like so

=AVERAGE(IF(A1:A5<>0,A1:A5))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"daniel" <dlipson@gmail.com> wrote in message
news:1109148217.909497.258000@l41g2000cwc.googlegroups.com...
> I have a spreadsheet with a few arrays in each of which there are a few
> zeros. I have used a number fuctions (median stdev, var, average etc)
> and now realize that I should have excluded the zeros. Is there any
> quick way to do this?
> Thanks
> Daniel
>