# conditional average with 2 conditions

#### Jess13

##### New Member
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

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.

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

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
ABCDEFG
1Field1Field2Filed3Field1Field2Avg
2A112A114.66667
3A11414.66667
4B220
5B316
6C118
7A216
8C312
9B214
10A118
Sheet1

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

Replies
1
Views
251
Replies
5
Views
284
Replies
8
Views
542
Replies
8
Views
382
Replies
9
Views
508

1,219,905
Messages
6,150,901
Members
450,991
Latest member
ExcelDoer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back