| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
function to compute average without zeros
I am not a programmer but need to write a formula that
will compute an average from a column but without the zeros. Or I need the zeros to become an empty cell through a formula. I have been researching this for hours and have come up blank. Thanks for all help Sherie |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function to compute average without zeros
If your cells' results are produced by formulas
=if(formlua=0,"",formula) then just use the average formula. -- Regards, Tom Ogilvy Sherie wrote in message ... I am not a programmer but need to write a formula that will compute an average from a column but without the zeros. Or I need the zeros to become an empty cell through a formula. I have been researching this for hours and have come up blank. Thanks for all help Sherie |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function to compute average without zeros
Sherie,
You could try this (assuming your data is in the range E5:E14): =AVERAGE(IF(E5:E14<0,E5:E14)) This formula is array-entered (ctrl-shift-enter) hth, Ryan -----Original Message----- I am not a programmer but need to write a formula that will compute an average from a column but without the zeros. Or I need the zeros to become an empty cell through a formula. I have been researching this for hours and have come up blank. Thanks for all help Sherie . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function to compute average without zeros
Hi Sherie,
With numbers in cells A1 through A6 with some cells containing zeros, this formula should give you what you want: =SUM(A1:A6)/COUNTIF(A1:A6,"<0") Paul "Ryan Poth" wrote in message ... Sherie, You could try this (assuming your data is in the range E5:E14): =AVERAGE(IF(E5:E14<0,E5:E14)) This formula is array-entered (ctrl-shift-enter) hth, Ryan -----Original Message----- I am not a programmer but need to write a formula that will compute an average from a column but without the zeros. Or I need the zeros to become an empty cell through a formula. I have been researching this for hours and have come up blank. Thanks for all help Sherie . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
function to compute average without zeros
Just a caution,
If any of the cells are blank, it will give an unexpected answer. Regards, Tom Ogilvy "Paul Simon" wrote in message om... Hi Sherie, With numbers in cells A1 through A6 with some cells containing zeros, this formula should give you what you want: =SUM(A1:A6)/COUNTIF(A1:A6,"<0") Paul "Ryan Poth" wrote in message ... Sherie, You could try this (assuming your data is in the range E5:E14): =AVERAGE(IF(E5:E14<0,E5:E14)) This formula is array-entered (ctrl-shift-enter) hth, Ryan -----Original Message----- I am not a programmer but need to write a formula that will compute an average from a column but without the zeros. Or I need the zeros to become an empty cell through a formula. I have been researching this for hours and have come up blank. Thanks for all help Sherie . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average Function that ignores zeros | Excel Worksheet Functions | |||
| Excel 2002: How to compute average, min and max figures? | Excel Discussion (Misc queries) | |||
| Conditional Statement to Compute Average | Excel Worksheet Functions | |||
| Average Function (include Blank Cells and Zeros) | Excel Discussion (Misc queries) | |||
| How do i compute the average value of 100 iterations? | Excel Worksheet Functions | |||
![Sherie[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
