Excel Statistical Notes: Percentiles and Weighted Means

Utilizing Excel for Statistical Calculations: Percentiles, Quartiles, and Weighted Means

Percentiles in Excel

  • Function Used: The built-in Excel function for calculating percentiles is PERCENTILE.EXC.
    • This function specifically matches the calculation formula found in the textbook.
  • Function Arguments:
    • Array: Represents the range of cells containing the data. For example, if data is in cells A1 through A12, this would be A1:A12.
    • k: Represents the percentile to be calculated. **Crucially, k must be entered as a decimal between 0 and 1 (e.g., 0.25 for the 25^{\text{th}} percentile, 0.50 for the 50^{\text{th}} percentile).
      • Entering k as a whole number will result in an error (e.g., #NUM!).
  • Benefits of Using the Function:
    • Eliminates the need for manually sorting the data.
    • Removes the requirement to perform complex mathematical steps outlined in the textbook for percentile calculation.
  • Copying Formulas:
    • When copying percentile formulas to other cells, it is essential to use absolute cell references for the data array (e.g., \$A\$1:\$A\$12) to ensure the data range remains constant.
    • After pasting, only the k argument needs to be edited to calculate a different percentile.
  • Editing Formulas:
    • Formulas can be edited directly in the formula bar by changing the k value (the second argument after the comma).
    • Alternatively, highlight the cell with the formula, click