Average non-adjacent cells if the cell does not contain zero

[Guest]

I have a spread sheet with data in non-adjacent cells. I would like to
average the data in the cells only if the cell does not contain a zero. How
would I do this?

Your help is greatly appreciated!!!

Cheri

 

[Guest]

Hello -
This formula will work, assume your values are in the range A1:A1000:

=AVERAGE(IF(A1:A1000<>0,A1:A1000,""))

This is an array formula so enter it in using ctrl+shift+enter.

 

[Biff]

Hi!

I have a spread sheet with data in non-adjacent cells.

We would need to know EXACTLY which cells you want to average.

Biff

 

[Guest]

Hi DaveB,

Thank you for the assistance. My problem is that there is other data in the
cells that are adjacent, so I cannot use a range. It must average different
specific cells in a row only if the cell does not contain a zero.

Thanks,
Cheri

 

[Guest]

One row would be averaging what is in cells b5, d5, f5, and h5.

I look forward to your response! Thanks!!!

 

[Biff]

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF((MOD(COLUMN(B5:H5),2)=0)*(B5:H5<>0),B5:H5))

Biff

 

[Guest]

Hi Biff,

That works GREAT! Now, is there a way to append the formula so that in the
event none of the cells have a greater than zero value that the cell will not
show the #DIV/0! error?

Thanks again!

 

[Biff]

Hi!

Try this (still an array):

=IF(SUMPRODUCT(--(MOD(COLUMN(B5:H5),2)=0),--(B5:H5>0))=0,"",AVERAGE(IF((MOD(COLUMN(B5:H5),2)=0)*(B5:H5<>0),B5:H5)))

If no values in the desired range are greater than zero the formula returns
a blank: ""

If you want something else to be returned just replace the "" in the
formula.

Biff

 

[Guest]

No, that didn't work <frown> If I input 95% and 100% in two of the cells the
answer with the first formula returns a correct average of 97.5%. With this
formula it returns a 96.2% and if I empty the cells or place a zero in them,
the 96.2% stays in the cell. I'm sure I should do something that would
update it, but it didn't recalculate on its own.

Thanks for trying...I can live with the #DIV/0!...it just isn't pretty <smile>

Thanks!!!

 

[Biff]

Hi!

Hmmm....

The formula DOES work! I'll send you a sample file to prove it if you'd
like!

Did you make sure to enter it as an array?

I can't duplicate what you're describing and it's working just fine for me.

Biff

 

[Guest]

That would be awesome if you could email me the sample file. I am at
(e-mail address removed)

Thanks!!!
Cheri

 

[Biff]

OK, sent a sample file.

Biff

That would be awesome if you could email me the sample file. I am at
(e-mail address removed)

Thanks!!!
Cheri