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

1 Answer 1

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)
5
  • 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, 2018 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, 2018 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, 2018 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. Jul 4, 2018 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, 2018 at 7:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .