Average non continguous cells, excluding zero's

[Guest]

I am trying to average cells that are non contingous, and also need to
exlcude any zero's in the mix. All the posts I have read count on either
sumproduct or countif both of which don't seem to be able to accept non
contingous cells. Is there something that might be able to help.

An example of the simple average formula is

=AVERAGE(E92,E71,E50,E29,E8)

Thanks,

Keith

 

[Peo Sjoblom]

You can't, it takes an array formula or a combination of sum and countif but
an array formula demands a range so does countif, there is one way using a
workaround, here's an example using different ranges


=SUMPRODUCT(--(LARGE((B1:B4,D1:E4,G1:G4),ROW(INDIRECT("1:"&COUNT(B1:B4,D1:E4,G1:G4))))<>0))

if they all are single cells just put a comma between them, so to get
average of the above range

=SUM(B1:B4,D1:E4,G1:G4)/SUMPRODUCT(--(LARGE((B1:B4,D1:E4,G1:G4),ROW(INDIRECT("1:"&COUNT(B1:B4,D1:E4,G1:G4))))<>0))

 

[Guest]

A brute force work around is to set up a column (say Y) as a dummy column and
enter
=E92 in Y1,=E71in Y2,etc.
you then have the contiguous cells necessary for Sumif or sum product methods.