Average Function that ignores zeros
That worked. Thanks!
"T. Valko" wrote:
Try this...
=IFERROR(SUM(B10,B22,B34)/INDEX(FREQUENCY((B10,B22,B34),0),2),"")
--
Biff
Microsoft Excel MVP
"Frustrated by Averages"
wrote in message ...
I am using 2007. There are no negative numbers. I would prefer that the
cell be blank in place of the error code.
Thanks.
"T. Valko" wrote:
What version of Excel are you using?
Will there be any negative numbers in the cells?
What result do you want to replace the error?
--
Biff
Microsoft Excel MVP
"Frustrated by Averages"
wrote in message
...
Thanks again. One more question...Is there any easy way to hide a
"#DIV/0"
error? As the spreadsheet is populated it will go away, so this is
more
viewing purposes while the spreadsheet is blank.
"Jacob Skaria" wrote:
Try..
=SUM(B10,B22,B34)/SUM(COUNTIF(INDIRECT({"B10","B22","B34"}),"0"))
--
Jacob
"Frustrated by Averages" wrote:
I am attempting to average three separate cells (B10, B22 and B34)
and
ignore
any zeros that exist. Each cell contains a formula that is
gathering
information from a pivot table. I need the average formula to
ignore
any
zeros that may be in these cells when calculating the average.
I have tried many different formulas, but nothing seems to work.
All
of the
solutions I have read related to this problem assume the cells are
in a
continuous range (e.g. B10;B34).
Any help would be appreciated.
.
.
|