Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maddening Dilemma - Compare each cell within column a to each cell in column b and find unique matches | Excel Worksheet Functions | |||
average one column based on criteria from another | Excel Discussion (Misc queries) | |||
how can I average more cells only in a column by a criteria? | Excel Worksheet Functions | |||
calculate average in a column based on criteria in another column | Excel Discussion (Misc queries) | |||
How do I compare cells in a column | New Users to Excel |