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.

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$22=1),(E$2:E2))/COUNTIF(D$22,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.

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$22=1),(E$2:E2))/COUNTIF(D$22,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.