Large with Average and multiple criteria

[Reboshua]

In F2, I'd like a formula that will calculate the average of the two largest values in column C provided that the value in column A = the value in E2 and the values in column B are greater than 3.

The result I expect from this calculation for cell f2 is 44

I've tried a handful of combinations of averageifs, if(and, etc and keep getting an error.

Thank you!

Unknown
[Table="width:, class:head"][tr=bgcolor:#888888][th]_Row\^Col[/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]Pos[/td][td]Min[/td][td]Pts[/td][td][/td][td=bgcolor:#F2F2F2]Position[/td][td=bgcolor:#F2F2F2]Top 2[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]

1​

[/td][td]

34​

[/td][td]

25​

[/td][td][/td][td=bgcolor:#F2F2F2]

1​

[/td][td=bgcolor:#F2F2F2][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]

1​

[/td][td]

14​

[/td][td]

11​

[/td][td][/td][td=bgcolor:#F2F2F2]

2​

[/td][td=bgcolor:#F2F2F2][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

1​

[/td][td]

3​

[/td][td]

4​

[/td][td][/td][td=bgcolor:#F2F2F2]

3​

[/td][td=bgcolor:#F2F2F2][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]

1​

[/td][td]

29​

[/td][td]

34​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]

1​

[/td][td]

38​

[/td][td]

54​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]

2​

[/td][td]

32​

[/td][td]

23​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td]

2​

[/td][td]

3​

[/td][td]

5​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td]

2​

[/td][td]

3​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td]

2​

[/td][td]

34​

[/td][td]

40​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

11​

[/td][td]

2​

[/td][td]

28​

[/td][td]

34​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

12​

[/td][td]

3​

[/td][td]

23​

[/td][td]

12​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

13​

[/td][td]

3​

[/td][td]

4​

[/td][td]

6​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

14​

[/td][td]

3​

[/td][td]

3​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

15​

[/td][td]

3​

[/td][td]

23​

[/td][td]

35​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

16​

[/td][td]

3​

[/td][td]

26​

[/td][td]

23​

[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[Tetra201]

Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=SUM(LARGE(IF(($A$2:$A$16=E2)*($B$2:$B$16>3),$C$2:$C$16),{1,2}))/2

 

[barry houdini]

Try this formula in F2 copied down

=AVERAGE(AGGREGATE(14,6,C$2:C$16/(A$2:A$16=E2)/(B$2:B$16>3),{1,2}))

 

[Reboshua]

Thank you, Barry and Tetra. Aggregate worked well and I had never used that before, so it will also help in other places too. Cheers to you both!