2

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

3

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

Worksheet Screenshot

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)
|improve this answer|||||
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.