average formula ingoring empty cells

  • Thread starter Thread starter jenparker1234
  • Start date Start date
J

jenparker1234

I need to create a formula to average several cells, but the cells are
not in a range.

So far I have =AVERAGE(G3, J3, M3, P3, S3), which works fine, but cells
M3,
P3 and S3 are empty (zero) until I have the data that I need. I want
the formula to already be set up for future input.

I've been able to make it work for a range of values (G3:S3), but not
with indivual cells in a group.

Help please! Thanks. :)
 
the average formula you posted will ignore blanks, but not zero values.
here is an alternative formula, which will average the cells you
requested, including only the non zero values. J is column 10, M is
column 13, etc.

=SUMPRODUCT((MOD(COLUMN(G3:S3)-1,3)=0)*(G3:S3<>0)*(G3:S3))/SUMPRODUCT((MOD(COLUMN(G3:S3)-1,3)=0)*(G3:S3<>0))
 
Hi!

Not sure what you're asking for.
cells M3, P3 and S3 are empty (zero)
Click to expand...

Are the cells EMPTY or do they contain the number 0?

AVERAGE will ignore the empty cells, they are not included as 0 values. Do
you want to exclude cells that contain the number 0?

Biff

"jenparker1234" <[email protected]>
wrote in message
 
another solution to ignore the zero values (or blanks) in the range you
requested - enter this with control+shift+enter

=AVERAGE(IF(MOD(COLUMN($G$3:$S$3)-1,3)=0,IF($G$3:$S$3>0,$G$3:$S$3)))
 
Back
Top