Averaging Cells Based On Conditions in Neighboring Cells

I need help in creating a formula to average data based on conditions
found in neighboring cells. I have data in cells C, I, O, U, AA, & AG.
I need to include each of these cells in the average calculation only
if the cell to its right is greater than zero.

Example:
Cells to average - C=5, I=7, O=9, U=3, AA=5, & AG=77
Neighboring cells - D=19, J=12, P=99, V=108, AB=8, & AH=0
The formula should avarage 5, 7, 9, 3, & 5, for a result of 5.8. 77 is
not included in the average calculation because the cell to its right,
AH, equals zero.

Any suggestions on how this can be done?




Thanks!




Sandi

There's probably a more elegant solution, but this works:

=(IF(D1>0,C1)+IF(J1>0,I1)+IF(P1>0,O1)+IF(V1>0,U1)+IF(AB1>0,AA1)+IF(AH1>0,AG1))/(IF(D1>0,1)+IF(J1>0,1)+IF(P1>0,1)+IF(V1>0,1)+IF(AB1>0,1)+IF(AH1>0,1))

HTH,
Elkar


"foofoo" wrote:

> I need help in creating a formula to average data based on conditions
> found in neighboring cells. I have data in cells C, I, O, U, AA, & AG.
> I need to include each of these cells in the average calculation only
> if the cell to its right is greater than zero.
>
> Example:
> Cells to average - C=5, I=7, O=9, U=3, AA=5, & AG=77
> Neighboring cells - D=19, J=12, P=99, V=108, AB=8, & AH=0
> The formula should avarage 5, 7, 9, 3, & 5, for a result of 5.8. 77 is
> not included in the average calculation because the cell to its right,
> AH, equals zero.
>
> Any suggestions on how this can be done?
>
>
>
>
> Thanks!
>
>
>
>
> Sandi
>
>

Assuming that C2:AH2 contains the data, try...

=AVERAGE(IF(MOD(COLUMN(D2:AH2)-COLUMN(D2),6)=0,IF(D2:AH2>0,C2:AG2)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <1150838246.468153.36820@p79g2000cwp.googlegroups.com>,
"foofoo" <foofoo99@yahoo.com> wrote:

> I need help in creating a formula to average data based on conditions
> found in neighboring cells. I have data in cells C, I, O, U, AA, & AG.
> I need to include each of these cells in the average calculation only
> if the cell to its right is greater than zero.
>
> Example:
> Cells to average - C=5, I=7, O=9, U=3, AA=5, & AG=77
> Neighboring cells - D=19, J=12, P=99, V=108, AB=8, & AH=0
> The formula should avarage 5, 7, 9, 3, & 5, for a result of 5.8. 77 is
> not included in the average calculation because the cell to its right,
> AH, equals zero.
>
> Any suggestions on how this can be done?
>
>
>
>
> Thanks!
>
>
>
>
> Sandi

Assuming that C2:AH2 contains the data, try...

=AVERAGE(IF(MOD(COLUMN(D2:AH2)-COLUMN(D2),6)=0,IF(D2:AH2>0,C2:AG2)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <1150838246.468153.36820@p79g2000cwp.googlegroups.com>,
"foofoo" <foofoo99@yahoo.com> wrote:

> I need help in creating a formula to average data based on conditions
> found in neighboring cells. I have data in cells C, I, O, U, AA, & AG.
> I need to include each of these cells in the average calculation only
> if the cell to its right is greater than zero.
>
> Example:
> Cells to average - C=5, I=7, O=9, U=3, AA=5, & AG=77
> Neighboring cells - D=19, J=12, P=99, V=108, AB=8, & AH=0
> The formula should avarage 5, 7, 9, 3, & 5, for a result of 5.8. 77 is
> not included in the average calculation because the cell to its right,
> AH, equals zero.
>
> Any suggestions on how this can be done?
>
>
>
>
> Thanks!
>
>
>
>
> Sandi