# Excel Formula To Show Top X Percentage Of An Array

Using the simple data set below, starting in A1, with headers:

``````Order   Time
a       1
b       1
c       2
d       4
e       4
f       5
g       6
h       7
i       500
j       600
``````

I want to know in what time are %80 of orders completed in.

In this example it would return 7, as it is the eight largest number, so %80 of orders are completed in 7 days or less.

What formula could I use to get this, considering:

1. The time array will contain 100s of order times.
2. The time array will not always be sorted in order.
3. I would prefer not to use a helper column or VBA, It has to be contained within in a single cell.
4. I can use an array formula, just remember the array can vary in size, so the array would need to account for this.

I have tried using `=PERCENTILE.EXC(B2:B11,0.8)` but that reutnred a value of 401.4

I also tried `=PERCENTILE.INC(B2:B11,0.8)` but that returned 105.6

The solution is pretty straightforward. It requires the `SMALL()` function:

Enter the following formula in `D2`:

``````=SMALL(B2:B11,ROWS(B2:B11)*0.8)
``````

Note that the `LARGE()` function could also be used but it is not as elegant:

``````=LARGE(B2:B11,ROWS(B2:B11)*(1-0.8)+1)
``````
• I had just got to something like that, my full formula is now `=SMALL(IF(Raw_Data!\$G\$2:\$G\$3942=Pivot!\$B4,Raw_Data!\$I\$2:\$I\$3942,""),COUNTIF(Raw_Data!G:G,Pivot!B4)*\$U\$2)` entered as an array. where the COUNTIF replaces ROWS etc. and U2 is in place of 0.8 – PeterH Jul 4 '18 at 11:13
• I also use IF on the Order column as i want to get top x percent for all order types etc. Thanks for you input, will accept as correct – PeterH Jul 4 '18 at 11:14
• @PeterH No probs. You can also use the `LARGE()` function as you can see from the edit to my answer. – robinCTS Jul 4 '18 at 11:19
• @PeterH, you can use this also, `=SMALL(H115:H124,ROWS(INDIRECT("1:10"))*0.8)` where "1:10" to "1:N" where N is the number of values in range. – Rajesh S Jul 4 '18 at 11:29
• @fixer1234 I normally do (at least for the more complicated solutions), but I get your point. If you hadn't brought it up I may still have considered the straight forward use of the `SMALL()` function too basic to explain, even if PeterH was a novice, as it can be understood just from Excel's formula help. However, what is basic and obvious to us is not necessarily so for others ;-) – robinCTS Jul 5 '18 at 7:53