Calculate averages not including zero values

[Guest]

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.

 

[Guest]

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

 

[kk]

Hi

Hope this help...

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

Confirmed the formula by pressing Ctrl + Shift + Enter


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.

 

[Guest]

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]

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


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....

 

[Guest]

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!

 

[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