calculate average from non contiguous column, ignoring (0) values
Jesse wrote...
....
. . . I have a need to have 0 in a range of cells (instead of blank). . . .
....
There could be alternatives.
How can I create an Average formula that ignores cells with 0 value?
But in case there aren't alternatives, the most general way would be
=SUM((B2,B9,B16,B23))/INDEX(FREQUENCY((B2,B9,B16,B23),{0}),2)
If your value cells were always every 7th row beginning with row 2,
you could use an array formula like
=AVERAGE(IF(MOD(ROW(B2:B23),7)=2,IF(B2:B230,B2:B2 3)))
If you don't know what an array formula is, look for it in online Help.
|