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:
- The time array will contain 100s of order times.
- The time array will not always be sorted in order.
- I would prefer not to use a helper column or VBA, It has to be contained within in a single cell.
- 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