Matching multiple k's for small function to get average

[dbcooper88]

Column E has data Im looking for that matches with column O. Looking for the 4 smallest numbers in column O that match with column E. Want to average the next numbers of matching column E. Heres what Ive come up with for one match:

=INDEX(E4:E66,MATCH(SMALL(O$4:O66,2),O4:O66,0)+1,1)

TIA

 

[sheetspread]

Excel 2010
	E	O	P	Q
1	Name	Number
2	Z	32	Z	13
3	W	86		14
4	E	77		30
5	Z	30		32
6	T	14
7	Y	87
8	U	5
9	Z	14
10	O	92
11	P	70
12	A	27
13	Z	75
14	Z	57
15	F	61
16	G	18
17	H	88
18	J	25
19	Z	63
20	L	76
21	Z	13
Sheet9

Cell Formulas
Range	Formula
Q2	{=SMALL(IF($E$2:$E$21=$P$2,$O$2:$O$21),ROW(A1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[dbcooper88]

Excel 2010

	E	O	P	Q
Name	Number
Z	Z
W
E
Z
T
Y
U
Z
O
P
A
Z
Z
F
G
H
J
Z
L
Z

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]32[/TD]

[TD="align: right"]13[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]86[/TD]
[TD="align: right"][/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]77[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"]32[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]87[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]92[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]70[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]75[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]57[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]61[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]88[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]63[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]76[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q2[/TH]
[TD="align: left"]{=SMALL(IF($E$2:$E$21=$P$2,$O$2:$O$21),ROW(A1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Looking for an average of the E's. Your E's arent even numbers. Please reread first post.

 

[mikerickson]

dbcooper88,
Could you please post an example of your data and the desired result?

 

[dbcooper88]

dbcooper88,
Could you please post an example of your data and the desired result?

	E	O
1	6	5
2	6	6
3	4	3
4	5	2
5	5	4
6	1	7
7	2	8
8	4	1

<tbody>
</tbody>

Want to average the numbers in E that match with the 2^nd, 3^rd, 4^th and 5^th smallest numbers in column O. But I want to average the next number in E. So in the above example want the result to be for column E the average of 6,5,5,1. Heres what Ive come up with for one match for 2nd smallest:

=INDEX(E4:E66,MATCH(SMALL(O$4:O66,2),O4:O66,0)+1,1)

 

[mikerickson]

Want to average the numbers in E that match with the 2^nd, 3^rd, 4^th and 5^th smallest numbers in column O. But I want to average the next number in E. So in the above example want the result to be for column E the average of 6,5,5,1. Heres what Ive come up with for one match for 2nd smallest:

=INDEX(E4:E66,MATCH(SMALL(O$4:O66,2),O4:O66,0)+1,1)

I don't understand how your description matches your desired result.

The 2nd,3rd,4th and 5th smallest numbers in column O are 2, 3, 4 and 5.
The numbers in column E on those rows are 5, 4, 5 and 6.
Which aren't the numbers that you say you want averaged.

 

[dbcooper88]

I don't understand how your description matches your desired result.

The 2nd,3rd,4th and 5th smallest numbers in column O are 2, 3, 4 and 5.
The numbers in column E on those rows are 5, 4, 5 and 6.
Which aren't the numbers that you say you want averaged.

Want to average the next numbers of the matching rows/numbers.

 

[sheetspread]

 

[sheetspread]

Something like:

Excel 2010
	E	F	G
1	Series1		Series2
2	6		5
3	6		6
4	4		3
5	5		2
6	5		4
7	1		7
8	2		8
9	4		1
10
11	4.25
Sheet10 (2)

Cell Formulas
Range	Formula
E11	{=AVERAGE(IF(ISNUMBER(MATCH($G$2:$G$9,SMALL($G$2:$G$9,{2;3;4;5}),0)),OFFSET($E$2:$E$9,1,0)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mikerickson]

Try
=AVERAGEIFS($E$2:$E$9, $O$1:$O$8,">"&MIN($O$1:$O$8), $O$1:$O$8,"<="&SMALL($O$1:$O$8,5))