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

Averaging Cells Based On Conditions in Neighboring Cells
 

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

=(IF(D10,C1)+IF(J10,I1)+IF(P10,O1)+IF(V10,U1)+ IF(AB10,AA1)+IF(AH10,AG1))/(IF(D10,1)+IF(J10,1)+IF(P10,1)+IF(V10,1)+IF(AB 10,1)+IF(AH10,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

Averaging Cells Based On Conditions in Neighboring Cells
 

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)+S UMIF(AH1,"<0",AG1))/(COUNTIF(D1,"<0")+COUNTIF(J1,"<0")+COUNTIF(P1,"< 0")+COUNTIF(V1,"<0")+COUNTIF(AB1,"<0")+COUNTIF( AH1,"<0"))


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=553823

Averaging Cells Based On Conditions in Neighboring Cells
 

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

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

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

Hope this helps!

In article . com,
"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