To get an average of a column of numbers not zero/blank cells
 

I am trying to set up a spreadsheet where the lenght of each call goes in a
column.
I need to the show average call lenght at the bottom of the chart.
As the figures are updated frequently I need a formula that will work out
the average of the data entered. When doing the =SUM(D3:D15)/COUNT(D3:D15),
it is counting the blank cells and giving a very false averge. I have tried
going into the excell advanced options and unticked the box SHOW A ZERO IN
CELLS THAT HAVE A ZERO VALUE.

I would have thought this would be quite easy to do. I am intermediatery at
ecel, or so I thought!

Help would be most greatfull

To get an average of a column of numbers not zero/blank cells
 

HI,
Try this modification to your formula:
=SUM(D3:D15)/COUNTIF(D3:D15,"0")
Regards - Dave.

To get an average of a column of numbers not zero/blank cells
 

BTW,
=AVERAGE(D3:D15) will ignore blank cells when calculating, but not cells
containing 0 (zero). If your column contains blanks but not 0's, this would
be the more efficient function to use.
Dave.

To get an average of a column of numbers not zero/blank cells
 

Why are you not just using

=AVERAGE(D3:D15)

Neither AVERAGE, SUM, nor COUNT will include blank cells, or even cells that
have spaces in them.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Neil" wrote in message
...
I am trying to set up a spreadsheet where the lenght of each call goes in a
column.
I need to the show average call lenght at the bottom of the chart.
As the figures are updated frequently I need a formula that will work out
the average of the data entered. When doing the
=SUM(D3:D15)/COUNT(D3:D15),
it is counting the blank cells and giving a very false averge. I have
tried
going into the excell advanced options and unticked the box SHOW A ZERO
IN
CELLS THAT HAVE A ZERO VALUE.

I would have thought this would be quite easy to do. I am intermediatery
at
ecel, or so I thought!

Help would be most greatfull

To get an average of a column of numbers not zero/blank cells
 

If it were that there were 0 values but zeroes are suppressed and you want
to not count zero, then use this array formula

=AVERAGE(IF(D3:D15<0,D3:D15))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Neil" wrote in message
...
I am trying to set up a spreadsheet where the lenght of each call goes in a
column.
I need to the show average call lenght at the bottom of the chart.
As the figures are updated frequently I need a formula that will work out
the average of the data entered. When doing the
=SUM(D3:D15)/COUNT(D3:D15),
it is counting the blank cells and giving a very false averge. I have
tried
going into the excell advanced options and unticked the box SHOW A ZERO
IN
CELLS THAT HAVE A ZERO VALUE.

I would have thought this would be quite easy to do. I am intermediatery
at
ecel, or so I thought!

Help would be most greatfull