Average Function that ignores zeros

[Frustrated by Averages]

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.

 

[Jacob Skaria]

Try..
=SUM(B10,B22,B34)/SUM(COUNTIF(INDIRECT({"B10","B22","B34"}),">0"))

 

[Jacob Skaria]

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)))

 

[Frustrated by Averages]

That worked. Thanks!!

Try..
=SUM(B10,B22,B34)/SUM(COUNTIF(INDIRECT({"B10","B22","B34"}),">0"))

 

[Frustrated by Averages]

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.

 

[Frustrated by Averages]

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!

 

[T. Valko]

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?

 

[Frustrated by Averages]

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]

Try this...

=IFERROR(SUM(B10,B22,B34)/INDEX(FREQUENCY((B10,B22,B34),0),2),"")

 

[Frustrated by Averages]

That worked. Thanks!

Try this...

=IFERROR(SUM(B10,B22,B34)/INDEX(FREQUENCY((B10,B22,B34),0),2),"")

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

You're welcome. Thanks for the feedback!