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