Subtotal function to pull top 5 values

[MarkTiger]

I am trying to pull the top 5 values from a list. The problem is the list has subtotals throughout, which I do not want to pull. Is there a way to use the Subtotal(4,range) function to select the top 4 items. I'm trying not to create a new column for ranking.

Thank you.

 

[sky1in5]

you can use the =large formula, =large(array,1) =large(array,2) =large(array,3) =large(array,4) =large(array,5)

 

[AlphaFrog]

Hi and welcome to the board.
<br />

Book1
	A	B	C	D	E
1		Values			Top 5 Vals
2		44		1	99
3		12		2	95
4		28		3	94
5		42		4	92
6		72		5	87
7		87
8		36
9		22
10		77
11	Subtotal	420
12		10
13		94
14		28
15		17
16	Subtotal	149
17		87
18		60
19		8
20		34
21	Subtotal	189
22		95
23		72
24		69
25		99
26		52
27		92
28		19
29		16
30	Subtotal	514
Sheet1

Cell Formulas
Range	Formula
E2	{=LARGE(IF($A$2:$A$30<>"Subtotal",$B$2:$B$30),D2)}
E3	{=LARGE(IF($A$2:$A$30<>"Subtotal",$B$2:$B$30),D3)}
E4	{=LARGE(IF($A$2:$A$30<>"Subtotal",$B$2:$B$30),D4)}
E5	{=LARGE(IF($A$2:$A$30<>"Subtotal",$B$2:$B$30),D5)}
E6	{=LARGE(IF($A$2:$A$30<>"Subtotal",$B$2:$B$30),D6)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MarkTiger]

Thank you. I went ahead and added a column to add "Subtotal" on the appropriate row and used the formula above.