Microsoft Office Development and Consultancy
 Home|

Excel

|VBA|C#|Finance|Tools|Newsletter|Feedback|Contact 
 Excel > Functions > Statistical > PERCENTILE

 

PERCENTILE(array, k)

 
 Returns the number that corresponds to a particular percentage from an array of numbers (inclusive).

 arrayThe array of values.
 kThe percentile value in the range 0..1 (inclusive).

 REMARKS
 
  • This is the same as QUARTILE except you are not locked into the fixed percentiles of 0, 25, 50, 75 and 100.
     
  • PERCENTILE.INC was added in Excel 2010 to replace this function.
     
  • PERCENTILE.EXC was added in Excel 2010 to handle the percentile value in an exclusive range.
     
  • If "array" is empty or contains more than 8,191 data points, then #NUM! is returned.
     
  • If "k" < 0, then #NUM! is returned.
     
  • If "k" > 1, then #NUM! is returned.
     
  • If "k" is not numeric, then #VALUE! is returned.
     
  • If "k" is not a multiple of 1/(n - 1), then this function interpolates to determine the value at the kth percentile.
     
  • Example 1 - What value would represent the 30% percentile in the array of numbers {1,2,3,4}.
     
  • Example 2 - What value would represent the 33% percentile in the array of numbers {1,2,3,4}.
     
  • Example 3 - What value would represent the 90% percentile in the array of numbers {1,2,3,4}.
     
  • Example 4 - What value would represent the 0% percentile in the array of numbers {1,2,3,4}.
     
  • Example 5 - What value would represent the 100% percentile in the array of numbers {1,2,3,4}.

     EXAMPLES
     
     A
    1=PERCENTILE({1,2,3,4},0.3) = 1.9
    2=PERCENTILE({1,2,3,4},0.33) = 2.0
    3=PERCENTILE({1,2,3,4},0.9) = 3.7
    4=PERCENTILE({1,2,3,4},0) = 1
    5=PERCENTILE(,0.3) = 0
    6=PERCENTILE({1,2,3,4},2) = #NUM!
    7=PERCENTILE({1,2,3,4},"some text") = #VALUE!
     

     Functions - P | Index - P | Office Online 2010 | 2007 | 2003 

     © Better Solutions Limited 04-Jun-2013Top