Calculate averages not including zero values

[rmellison]

I have a large array of data which has intermittent zero values in it where
the data has not been acquired. I want to create a generic formula which I
can copy down one column that gives me the median average of the data in one
row, but does not include zero values. I have been able to do this for the
mean average by using the formula =(SUM(A1:Z1))/(COUNTIF(A1:Z1, "<>0")), (if
there is an easier way, let me know) but I cannot see a way of doing
something similar for the median. BTW, i have tried deselecting 'zero values'
in the options tab but it merely hides the zeroes, and still uses them in the
calculations.

Any thoughts gladly appreciated.

[Gary's Student]

Try empting cels containing zero. Use Find / Replace. I know that
AVERAGE(), for example, will ignore empty cells, but include zeros.
--
Gary's Student


"rmellison" wrote:

> I have a large array of data which has intermittent zero values in it where
> the data has not been acquired. I want to create a generic formula which I
> can copy down one column that gives me the median average of the data in one
> row, but does not include zero values. I have been able to do this for the
> mean average by using the formula =(SUM(A1:Z1))/(COUNTIF(A1:Z1, "<>0")), (if
> there is an easier way, let me know) but I cannot see a way of doing
> something similar for the median. BTW, i have tried deselecting 'zero values'
> in the options tab but it merely hides the zeroes, and still uses them in the
> calculations.
>
> Any thoughts gladly appreciated.

[kk]

Hi

Hope this help...

=Average(If(A1:Z1=0,"",A1:Z1))

Confirmed the formula by pressing Ctrl + Shift + Enter


"rmellison" <[email protected]> wrote in message
news:[email protected]...
I have a large array of data which has intermittent zero values in it where
the data has not been acquired. I want to create a generic formula which I
can copy down one column that gives me the median average of the data in one
row, but does not include zero values. I have been able to do this for the
mean average by using the formula =(SUM(A1:Z1))/(COUNTIF(A1:Z1, "<>0")), (if
there is an easier way, let me know) but I cannot see a way of doing
something similar for the median. BTW, i have tried deselecting 'zero
values'
in the options tab but it merely hides the zeroes, and still uses them in
the
calculations.

Any thoughts gladly appreciated.

[rmellison]

That also works, thanks. But I'm curious, what does Ctrl+Shift+Enter do to
the formula? Does that just have the effect of ignoring zeroes or does it
serve some other purpose? It may be useful again....


"kk" wrote:

> Hi
>
> Hope this help...
>
> =Average(If(A1:Z1=0,"",A1:Z1))
>
> Confirmed the formula by pressing Ctrl + Shift + Enter
>
>
> "rmellison" <[email protected]> wrote in message
> news:[email protected]...
> I have a large array of data which has intermittent zero values in it where
> the data has not been acquired. I want to create a generic formula which I
> can copy down one column that gives me the median average of the data in one
> row, but does not include zero values. I have been able to do this for the
> mean average by using the formula =(SUM(A1:Z1))/(COUNTIF(A1:Z1, "<>0")), (if
> there is an easier way, let me know) but I cannot see a way of doing
> something similar for the median. BTW, i have tried deselecting 'zero
> values'
> in the options tab but it merely hides the zeroes, and still uses them in
> the
> calculations.
>
> Any thoughts gladly appreciated.
>
>
>

[kk]

Hi,

This is an array formula.

From Excel Help:

A formula that performs multiple calculations on one or more sets of values,
and then returns either a single result or multiple results. Array formulas
are enclosed between braces { } and are entered by pressing
CTRL+SHIFT+ENTER.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"rmellison" <[email protected]> wrote in message
news:[email protected]...
That also works, thanks. But I'm curious, what does Ctrl+Shift+Enter do to
the formula? Does that just have the effect of ignoring zeroes or does it
serve some other purpose? It may be useful again....


"kk" wrote:

> Hi
>
> Hope this help...
>
> =Average(If(A1:Z1=0,"",A1:Z1))
>
> Confirmed the formula by pressing Ctrl + Shift + Enter
>
>
> "rmellison" <[email protected]> wrote in message
> news:[email protected]...
> I have a large array of data which has intermittent zero values in it
> where
> the data has not been acquired. I want to create a generic formula which I
> can copy down one column that gives me the median average of the data in
> one
> row, but does not include zero values. I have been able to do this for the
> mean average by using the formula =(SUM(A1:Z1))/(COUNTIF(A1:Z1, "<>0")),
> (if
> there is an easier way, let me know) but I cannot see a way of doing
> something similar for the median. BTW, i have tried deselecting 'zero
> values'
> in the options tab but it merely hides the zeroes, and still uses them in
> the
> calculations.
>
> Any thoughts gladly appreciated.
>
>
>

[JK]

I'm trying to do the same thing.
Calculate averages while skipping certain rows and all zero values...

(Example)

MILEAGE TOTAL
A1 100
A2 200
A3 300
A4 TOTAL: 600
A5 100
A6 0
A7 200
A8 TOTAL: 300
etc.. (down)

All of these cells are linked to another workbook. For some reason it
inserts a zero by default. The goal is to calculate the averge miles
traveled. I'm trying to have the function ignore the zeros. I've tried both
of the suggested formulas, but it looks like I have too many arguments? Can't
do, A1:A3, A5:A7, etc...?

Any help would be much appreciated...

Thx!


"kk" wrote:

> Hi,
>
> This is an array formula.
>
> From Excel Help:
>
> A formula that performs multiple calculations on one or more sets of values,
> and then returns either a single result or multiple results. Array formulas
> are enclosed between braces { } and are entered by pressing
> CTRL+SHIFT+ENTER.
>
> Take a look at here:
> http://www.cpearson.com/excel/array.htm
> http://www.emailoffice.com/excel/arrays-bobumlas.html
>
> kk
>
>
> "rmellison" <[email protected]> wrote in message
> news:[email protected]...
> That also works, thanks. But I'm curious, what does Ctrl+Shift+Enter do to
> the formula? Does that just have the effect of ignoring zeroes or does it
> serve some other purpose? It may be useful again....
>
>
> "kk" wrote:
>
> > Hi
> >
> > Hope this help...
> >
> > =Average(If(A1:Z1=0,"",A1:Z1))
> >
> > Confirmed the formula by pressing Ctrl + Shift + Enter
> >
> >
> > "rmellison" <[email protected]> wrote in message
> > news:[email protected]...
> > I have a large array of data which has intermittent zero values in it
> > where
> > the data has not been acquired. I want to create a generic formula which I
> > can copy down one column that gives me the median average of the data in
> > one
> > row, but does not include zero values. I have been able to do this for the
> > mean average by using the formula =(SUM(A1:Z1))/(COUNTIF(A1:Z1, "<>0")),
> > (if
> > there is an easier way, let me know) but I cannot see a way of doing
> > something similar for the median. BTW, i have tried deselecting 'zero
> > values'
> > in the options tab but it merely hides the zeroes, and still uses them in
> > the
> > calculations.
> >
> > Any thoughts gladly appreciated.
> >
> >
> >
>
>
>

[Dav]

Depending what and where totals appear

=SUM(IF(a1:a10<>0,IF(ISNUMBER(a1:a10),a1:a10,"")))

Entered as an array shift ctrl enter

Regards

Dav