| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate average and not include zero values
I have a final issue with my measurements sheet. I have several columns
designed to calculate how long it has taken people to do something ie, lines minutes lines per hr I then have a list of people and at the bottom of this list it then calculates the average no of lines per hr, trouble is it includes any zeroes also in the list, so instead of giving average of say the 2 people who did job it also includes 0 from the other 9? The formula i have at the moment is =SUM(IF(ISNUMBER(E5:E13),E5:E13,0))/COUNT(E5:E13) originally this was due to #DIV/0 issues which have been sorted. if i just use =AVERAGE(E5:E13) i just get the same answer now, can anyone help with my problem please, Thanks, |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate average and not include zero values
=SUMIF(E5:E13,"0")/COUNTIF(E5:E13,"0")
Bob Umlas Excel MVP "k1ngy" wrote: I have a final issue with my measurements sheet. I have several columns designed to calculate how long it has taken people to do something ie, lines minutes lines per hr I then have a list of people and at the bottom of this list it then calculates the average no of lines per hr, trouble is it includes any zeroes also in the list, so instead of giving average of say the 2 people who did job it also includes 0 from the other 9? The formula i have at the moment is =SUM(IF(ISNUMBER(E5:E13),E5:E13,0))/COUNT(E5:E13) originally this was due to #DIV/0 issues which have been sorted. if i just use =AVERAGE(E5:E13) i just get the same answer now, can anyone help with my problem please, Thanks, |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate average and not include zero values
K1ngy
Just thinking off the top of my head.....you could use the CountIF and set the criteria to be greater than 0, this will then just count the values above and ingonre all the zero values CountIF(E5:E13,"0")....have checked this out with a column of 5 numers three were 1s, two were 0 and it's worked... Hope this helps Regards Steve "k1ngy" wrote: I have a final issue with my measurements sheet. I have several columns designed to calculate how long it has taken people to do something ie, lines minutes lines per hr I then have a list of people and at the bottom of this list it then calculates the average no of lines per hr, trouble is it includes any zeroes also in the list, so instead of giving average of say the 2 people who did job it also includes 0 from the other 9? The formula i have at the moment is =SUM(IF(ISNUMBER(E5:E13),E5:E13,0))/COUNT(E5:E13) originally this was due to #DIV/0 issues which have been sorted. if i just use =AVERAGE(E5:E13) i just get the same answer now, can anyone help with my problem please, Thanks, |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate average and not include zero values
=AVERAGE(IF(E5:E130,E5:E13))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "k1ngy" wrote in message ... I have a final issue with my measurements sheet. I have several columns designed to calculate how long it has taken people to do something ie, lines minutes lines per hr I then have a list of people and at the bottom of this list it then calculates the average no of lines per hr, trouble is it includes any zeroes also in the list, so instead of giving average of say the 2 people who did job it also includes 0 from the other 9? The formula i have at the moment is =SUM(IF(ISNUMBER(E5:E13),E5:E13,0))/COUNT(E5:E13) originally this was due to #DIV/0 issues which have been sorted. if i just use =AVERAGE(E5:E13) i just get the same answer now, can anyone help with my problem please, Thanks, |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate average and not include zero values
Hi thats it thanks,
I better get some brownie points from work for this, Steve. "Bob Umlas, Excel MVP" wrote: =SUMIF(E5:E13,"0")/COUNTIF(E5:E13,"0") Bob Umlas Excel MVP "k1ngy" wrote: I have a final issue with my measurements sheet. I have several columns designed to calculate how long it has taken people to do something ie, lines minutes lines per hr I then have a list of people and at the bottom of this list it then calculates the average no of lines per hr, trouble is it includes any zeroes also in the list, so instead of giving average of say the 2 people who did job it also includes 0 from the other 9? The formula i have at the moment is =SUM(IF(ISNUMBER(E5:E13),E5:E13,0))/COUNT(E5:E13) originally this was due to #DIV/0 issues which have been sorted. if i just use =AVERAGE(E5:E13) i just get the same answer now, can anyone help with my problem please, Thanks, |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate average and not include zero values
Glad to have been of assistance...it just bugs the pants off you when you
can't solve the little bits Regards Again Steve (UK) "k1ngy" wrote: Hi thats it thanks, I better get some brownie points from work for this, Steve. "Bob Umlas, Excel MVP" wrote: =SUMIF(E5:E13,"0")/COUNTIF(E5:E13,"0") Bob Umlas Excel MVP "k1ngy" wrote: I have a final issue with my measurements sheet. I have several columns designed to calculate how long it has taken people to do something ie, lines minutes lines per hr I then have a list of people and at the bottom of this list it then calculates the average no of lines per hr, trouble is it includes any zeroes also in the list, so instead of giving average of say the 2 people who did job it also includes 0 from the other 9? The formula i have at the moment is =SUM(IF(ISNUMBER(E5:E13),E5:E13,0))/COUNT(E5:E13) originally this was due to #DIV/0 issues which have been sorted. if i just use =AVERAGE(E5:E13) i just get the same answer now, can anyone help with my problem please, Thanks, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Don't include errors in average | Excel Worksheet Functions | |||
| How do I include average costs in my charts? | Charts and Charting in Excel | |||
| Average Function (include Blank Cells and Zeros) | Excel Discussion (Misc queries) | |||
| Looking-up Columns w/calc'd Values ONLY to Calculate Average | Excel Worksheet Functions | |||
| in a pivot table, can the average include blank entries? | Excel Discussion (Misc queries) | |||
Linear Mode
