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

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.

--
Regards,

Dave


"Cheri" wrote:

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

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


"DaveB" wrote:

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.

--
Regards,

Dave


"Cheri" wrote:

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

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

"Cheri" wrote in message
...
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

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

I look forward to your response! Thanks!!!

"Cheri" wrote:

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

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

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

I look forward to your response! Thanks!!!

"Cheri" wrote:

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

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!

"Cheri" wrote:

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

Hi!

Try this (still an array):

=IF(SUMPRODUCT(--(MOD(COLUMN(B5:H5),2)=0),--(B5:H50))=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

"Cheri" wrote in message
...
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!

"Cheri" wrote:

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

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



"Cheri" wrote:

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

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

"Cheri" wrote in message
...
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!!!



"Cheri" wrote:

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

That would be awesome if you could email me the sample file. I am at


Thanks!!!
Cheri

"Biff" wrote:

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

"Cheri" wrote in message
...
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!!!



"Cheri" wrote:

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

OK, sent a sample file.

Biff

"Cheri" wrote in message
...
That would be awesome if you could email me the sample file. I am at


Thanks!!!
Cheri

"Biff" wrote:

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

"Cheri" wrote in message
...
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!!!



"Cheri" wrote:

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