Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct?
Can anyone provide a way to perform the following function. I've been told
there is a way to do with "sumproduct" which I use. Column A and B each have values in 100+ rows. Each cell in the column has one of several different #'s in them. If there is a "1" in the cell of a given row in in column A and a "2" in the cell of the same row in column B, what is the average of values cells of the same row in column C. i.e. i.e. give the average of values in column C restricting to rows where there is a "1" in column A and a "2" in column B. Can this function be done in Excel? Or am i stuck with SPSS or some such. anand |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct?
This is not a SUMPRODUCT solution, but it should work:
=AVERAGE(IF(A1:A200=1,IF(B1:B200=2,C1:C200))) This is an array formula. You need to press Ctl+Shift+Enter to make it work (not jsut Enter). If you entered it correctly, you will see {=AVERAGE(IF(A1:A200=1,IF(B1:B200=2,C1:C200)))} in the formula bar (you don't type the {} yourself). Adjust the range size to your needs. -- tj "anand" wrote: Can anyone provide a way to perform the following function. I've been told there is a way to do with "sumproduct" which I use. Column A and B each have values in 100+ rows. Each cell in the column has one of several different #'s in them. If there is a "1" in the cell of a given row in in column A and a "2" in the cell of the same row in column B, what is the average of values cells of the same row in column C. i.e. i.e. give the average of values in column C restricting to rows where there is a "1" in column A and a "2" in column B. Can this function be done in Excel? Or am i stuck with SPSS or some such. anand |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct?
Hi
You have an array solution from tj which gives you your required result. You specifically asked about doing it with Sumproduct, (which is not an array solution), so for completeness here is a Sumproduct answer =SUMPRODUCT(--(A1:A20=1),--(B1:B20=2),C1:C20)/SUMPRODUCT(--(A1:A20=1),--(B1:B20=2)) Change ranges to suit Regards Roger Govier anand wrote: Can anyone provide a way to perform the following function. I've been told there is a way to do with "sumproduct" which I use. Column A and B each have values in 100+ rows. Each cell in the column has one of several different #'s in them. If there is a "1" in the cell of a given row in in column A and a "2" in the cell of the same row in column B, what is the average of values cells of the same row in column C. i.e. i.e. give the average of values in column C restricting to rows where there is a "1" in column A and a "2" in column B. Can this function be done in Excel? Or am i stuck with SPSS or some such. anand |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |