Average Function that ignores zeros
 

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.

Average Function that ignores zeros
 

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.

Average Function that ignores zeros
 

Try the below array formula. Apply using Ctrl+Shift+Enter instead of Enter

=AVERAGE(IF(MOD(ROW(B10:B34),12)=10,IF(B10:B34<0, B10:B34)))

--
Jacob


"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.

Average Function that ignores zeros
 

That worked. Thanks!!

"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.

Average Function that ignores zeros
 

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.

Average Function that ignores zeros
 

Thanks again! That worked. Can you give me brief explanation of how this
formula is constructed as I wold like to recreate it in other scenarios?

Thanks!

"Jacob Skaria" wrote:

Try the below array formula. Apply using Ctrl+Shift+Enter instead of Enter

=AVERAGE(IF(MOD(ROW(B10:B34),12)=10,IF(B10:B34<0, B10:B34)))

--
Jacob


"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.

Average Function that ignores zeros
 

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.

Average Function that ignores zeros
 

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.


.

Average Function that ignores zeros
 

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.


.

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.


.



.

Average Function that ignores zeros
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Frustrated by Averages"
wrote in message ...
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.


.



.