Averaging Cells Based On Conditions in Neighboring Cells

[foofoo]

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

 

[Guest]

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

 

[Bearacade]

Just because I lack imagination, HTH

I am assuming these are on row 1:

=(SUMIF(D1,"<>0",C1)+SUMIF(J1,"<>0",I1)+SUMIF(P1,"<>0",O1)+SUMIF(V1,"<>0",U1)+SUMIF(AB1,"<>0",AA1)+SUMIF(AH1,"<>0",AG1))/(COUNTIF(D1,"<>0")+COUNTIF(J1,"<>0")+COUNTIF(P1,"<>0")+COUNTIF(V1,"<>0")+COUNTIF(AB1,"<>0")+COUNTIF(AH1,"<>0")

 

[Domenic]

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!