average formula ingoring empty cells

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

[duane]

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

[Biff]

Hi!

Not sure what you're asking for.

>cells M3, P3 and S3 are empty (zero)

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
news:[email protected]...
>
> 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.
>
>
> --
> jenparker1234
> ------------------------------------------------------------------------
> jenparker1234's Profile:
> http://www.excelforum.com/member.php...o&userid=36266
> View this thread: http://www.excelforum.com/showthread...hreadid=560539
>

[duane]

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