Excel Percentile

Excel: PERCENTILE.INC(A1:A5,0.2) = 1.6

 

Steps:

1.       a =  0, 2, 3, 4, 5

2.       percent = 0.2

3.       n = 5

4.       k = TRUNC(percent * (n – 1)  + 1) = TRUNC(0.2 * 4 + 1) = TRUNC (1.8) = 1

5.       f = percent * (n-1) – TRUNC(percent*(n-1)) = 0.8 – TRUNC(0.8) = 0.8

6.       output = a[k] + (f*a[k+1] – a[k]) = a[1] + (0.8 * a[2] – a[1])

= 0 + (0.8 * (2 – 0)) = 1.6

 

Reference: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q103493