Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average highest 16 numbers on a column of 32 numbers | Excel Worksheet Functions | |||
Ignoring blank cells on getting an average | Excel Discussion (Misc queries) | |||
Formula to average the last 4 non-blank numerical cells of a row? | Excel Worksheet Functions | |||
calculating average with blank cells | Excel Worksheet Functions | |||
30 Day Moving Average Ignoring Blank Cells | Excel Worksheet Functions |