compare cells in column to criteria, then average next column cell
 

I have a spreadsheet that has two columns. I want to search through the
first column for a specific value, and if the value is met, average the value
in the cell adjacent in the next column to previous values returned. The
formula
=IF(ISERROR(AVERAGE(IF(D$2:$D$1001=1,$E$2:$E$1001) )),0,(AVERAGE(IF($D$2:=1,$E$2:$E$1001))))
is supposed look for a 1 in column D, and if it is there, add the value in
column E to a running average. It does not work.

compare cells in column to criteria, then average next column cell
 

If you are just looking for an overall overage of the values in column E if
the value in column D meets certain criteria, you can use the following
formula:
=SUMPRODUCT(--($D$2:$D$1001=1),($E$2:$E$1001))/COUNTIF($D$2:$D$1001,1)

If, however, say in column F, you are looking for a 'running' average of the
values in column E if column D meets certain criteria, you could type the
following in column F2 and copy down as needed:
=IF(OR(D2="",E2=""),"",IF(D2=1,SUMPRODUCT(--(D$2:D2=1),(E$2:E2))/COUNTIF(D$2:D2,1),""))

--
John C


"Bradwin" wrote:

I have a spreadsheet that has two columns. I want to search through the
first column for a specific value, and if the value is met, average the value
in the cell adjacent in the next column to previous values returned. The
formula
=IF(ISERROR(AVERAGE(IF(D$2:$D$1001=1,$E$2:$E$1001) )),0,(AVERAGE(IF($D$2:=1,$E$2:$E$1001))))
is supposed look for a 1 in column D, and if it is there, add the value in
column E to a running average. It does not work.

compare cells in column to criteria, then average next column
 

Thank you, the first formula below works quite well.

"John C" wrote:

If you are just looking for an overall overage of the values in column E if
the value in column D meets certain criteria, you can use the following
formula:
=SUMPRODUCT(--($D$2:$D$1001=1),($E$2:$E$1001))/COUNTIF($D$2:$D$1001,1)

If, however, say in column F, you are looking for a 'running' average of the
values in column E if column D meets certain criteria, you could type the
following in column F2 and copy down as needed:
=IF(OR(D2="",E2=""),"",IF(D2=1,SUMPRODUCT(--(D$2:D2=1),(E$2:E2))/COUNTIF(D$2:D2,1),""))

--
John C


"Bradwin" wrote:

I have a spreadsheet that has two columns. I want to search through the
first column for a specific value, and if the value is met, average the value
in the cell adjacent in the next column to previous values returned. The
formula
=IF(ISERROR(AVERAGE(IF(D$2:$D$1001=1,$E$2:$E$1001) )),0,(AVERAGE(IF($D$2:=1,$E$2:$E$1001))))
is supposed look for a 1 in column D, and if it is there, add the value in
column E to a running average. It does not work.