Average non-adjacent cells & exclude zeros
 

I want to find an average of three cells that are non-adjacent and will
occasionally have a zero in it.
Example:
=AVERAGE(H12,V12,AJ12)

How can I rewrite this so that the formula will not include the cell in the
average if its value is zero?

Thanks!

Average non-adjacent cells & exclude zeros
 

One way

=AVERAGE(IF((MOD(COLUMN(H12:AJ12),14)=8)*(H12:AJ12 <0),H12:AJ12))


needs to be entered with ctrl + shift & enter

this assumes you have the same number of columns between each value like you
have in your example


--


Regards,


Peo Sjoblom


"Danni2004" wrote in message
...
I want to find an average of three cells that are non-adjacent and will
occasionally have a zero in it.
Example:
=AVERAGE(H12,V12,AJ12)

How can I rewrite this so that the formula will not include the cell in
the
average if its value is zero?

Thanks!

Average non-adjacent cells & exclude zeros
 

Thanks Peo.
Can you clarify what the "8" is for in this formula?
Also, I've seen other posts with the Ctrl + Shift & Enter before. Can you
explain why you have to use this?

"Peo Sjoblom" wrote:

One way

=AVERAGE(IF((MOD(COLUMN(H12:AJ12),14)=8)*(H12:AJ12 <0),H12:AJ12))


needs to be entered with ctrl + shift & enter

this assumes you have the same number of columns between each value like you
have in your example


--


Regards,


Peo Sjoblom


"Danni2004" wrote in message
...
I want to find an average of three cells that are non-adjacent and will
occasionally have a zero in it.
Example:
=AVERAGE(H12,V12,AJ12)

How can I rewrite this so that the formula will not include the cell in
the
average if its value is zero?

Thanks!

Average non-adjacent cells & exclude zeros
 

What are the column numbers for H12, V12, and AJ12? What does
MOD(column_number,14) return in each of those cases?

Control Shift Enter is used to enter an array formula. look up array
formula in Excel help.
--
David Biddulph

"Danni2004" wrote in message
...
Thanks Peo.
Can you clarify what the "8" is for in this formula?
Also, I've seen other posts with the Ctrl + Shift & Enter before. Can you
explain why you have to use this?

"Peo Sjoblom" wrote:

One way

=AVERAGE(IF((MOD(COLUMN(H12:AJ12),14)=8)*(H12:AJ12 <0),H12:AJ12))

needs to be entered with ctrl + shift & enter

this assumes you have the same number of columns between each value like
you
have in your example

"Danni2004" wrote in message
...
I want to find an average of three cells that are non-adjacent and will
occasionally have a zero in it.
Example:
=AVERAGE(H12,V12,AJ12)

How can I rewrite this so that the formula will not include the cell in
the
average if its value is zero?

Thanks!