Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,358
Default 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.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Maddening Dilemma - Compare each cell within column a to each cell in column b and find unique matches [email protected] Excel Worksheet Functions 66 May 1st 23 03:44 AM
average one column based on criteria from another ba374 Excel Discussion (Misc queries) 1 October 2nd 07 05:39 PM
how can I average more cells only in a column by a criteria? Alinutza Excel Worksheet Functions 3 October 11th 06 06:58 PM
calculate average in a column based on criteria in another column sharon t Excel Discussion (Misc queries) 2 May 12th 06 06:07 PM
How do I compare cells in a column Jim K New Users to Excel 2 January 30th 05 12:59 AM


All times are GMT +1. The time now is 07:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"