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!

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" <(E-Mail Removed)> wrote in message
news:77157F53-DC0A-4AC4-A082-(E-Mail Removed)...
>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!

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" <(E-Mail Removed)> wrote in message
> news:77157F53-DC0A-4AC4-A082-(E-Mail Removed)...
> >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!
>
>
>

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" <(E-Mail Removed)> wrote in message
news:69EC30C4-0D5C-4DE2-93A9-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:77157F53-DC0A-4AC4-A082-(E-Mail Removed)...
>> >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!
>>
>>
>>