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