conditional average with 2 conditions

[Jess13]

Hi all. I've seen a number of helpful posts where someone has one condition and needs to average numbers in a different column based on that one condition. Well, I've got 2 conditions. Before now, I'd used something like {=AVERAGE(IF(A1:A10=x, B1:B10)} to do one condition. Is there anything similar for 2 columns of conditional data?
I've got A1:A20 with the variables A, B, C, and D. And I've got B1:B20 with variables 1, 2, and 3. Then I've got C1:C20 with the values to be conditonally averaged...
Any help would be appreciated, and apologies if this is a duplicate question!
Jessica

 

[Dave Patton]

On 2002-08-27 10:06, Jess13 wrote:
Hi all. I've seen a number of helpful posts where someone has one condition and needs to average numbers in a different column based on that one condition. Well, I've got 2 conditions. Before now, I'd used something like {=AVERAGE(IF(A1:A10=x, B1:B10)} to do one condition. Is there anything similar for 2 columns of conditional data?
I've got A1:A20 with the variables A, B, C, and D. And I've got B1:B20 with variables 1, 2, and 3. Then I've got C1:C20 with the values to be conditonally averaged...
Any help would be appreciated, and apologies if this is a duplicate question!
Jessica

try
=AVERAGE(IF(A1:A10={"A","B"},B1:B10))

Sorry I did not read carefully.
See Aladin's solution.

Aove is applicable for 2 criteria within one column.





Please report back if it works OK.

Dave
This message was edited by Dave Patton on 2002-08-27 11:18

 

[Aladin Akyurek]

On 2002-08-27 10:06, Jess13 wrote:
Hi all. I've seen a number of helpful posts where someone has one condition and needs to average numbers in a different column based on that one condition. Well, I've got 2 conditions. Before now, I'd used something like {=AVERAGE(IF(A1:A10=x, B1:B10)} to do one condition. Is there anything similar for 2 columns of conditional data?
I've got A1:A20 with the variables A, B, C, and D. And I've got B1:B20 with variables 1, 2, and 3. Then I've got C1:C20 with the values to be conditonally averaged...
Any help would be appreciated, and apologies if this is a duplicate question!
Jessica

Let E2 house "A", F2 1.

=AVERAGE(IF((A2:A10=E2)*(B2:B10=F2),C2:C10))

array-entered, would give the average of C-values that co-occur with A-values that are E2 and B-values that are F2.

To array-enter a formula you need to hit control+shift+enter at the same time, not just enter.

An alternative is to use DAVERAGE, which requires that your data has labels at the top and you set up a criteria range.

Let A1 house the label Field1, B1 Field2, and C1 Field3.

In E1 enter: Field1
E2 and F2 are same as above: A and 1, respectively.

=DAVERAGE(A1:C10,3,E1:F2)

where 3 stands for the C-range to average will produce the same result as the array-formula.

See the figure...

aaMultCondAvg Jess13.xls
	A	B	C	D	E	F	G
1	Field1	Field2	Filed3		Field1	Field2	Avg
2	A	1	12		A	1	14.66667
3	A	1	14				14.66667
4	B	2	20
5	B	3	16
6	C	1	18
7	A	2	16
8	C	3	12
9	B	2	14
10	A	1	18
Sheet1

 

[Jess13]

Wonderful! I was messing around with all sorts of formulas, but I just kept missing the right one.
Works perfectly...
Thanks!
Jessica