Average Function that ignores zeros

  • Thread starter Thread starter Frustrated by Averages
  • Start date Start date
F

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.
 
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)))
 
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.
 
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!
 
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?
 
I am using 2007. There are no negative numbers. I would prefer that the
cell be blank in place of the error code.

Thanks.
 
Back
Top