Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel TRANSPOSE Function
  • Two-column Lookup in Excel
  • How to use Excel VLOOKUP Function
  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • Vlookup Examples in Excel

Data Analysis Examples

  • How to count table columns in Excel
  • How to create Checklist in Excel
  • Excel Bar Chart
  • How to create dynamic reference table name in Excel
  • Error Bars in Excel

Data Validation Examples

  • Excel Data validation whole percentage only
  • Excel Data validation must not contain
  • Prevent invalid data entering in specific cells
  • Excel Data validation date in next 30 days
  • Excel Data validation allow uppercase only

PERCENTILE and QUARTILE function examples in Excel

by

This example teaches you how to use the PERCENTILE and QUARTILE function in Excel. Below you can find a list of scores (green fill for illustration only).

1. Use the PERCENTILE function shown below to calculate the 30th percentile. Excel returns the value 12.7. This means that 30% (6 out of 20) of the scores are lower or equal to 12.7

Worked Example:   Calculate Conditional Percentile 'IF' in table in Excel

Note: The second argument of the PERCENTILE function must be a decimal number between 0 and 1. Excel uses a slightly different algorithm to calculate percentiles and quartiles than you find in most statistics books. If you’re interested, download the Excel file.

2. Use the PERCENTILE function shown below to calculate the 90th percentile. Excel returns the value 61.7. This means that 90% (18 out of 20) of the scores are lower or equal to 61.7

Worked Example:   Highlight data by quartile in Excel

3. Use the QUARTILE function shown below to calculate the 1st quartile. Excel returns the value 11.25. This means that 25% (5 out of 20) of the scores are lower or equal to 11.25

Note: The second argument of the QUARTILE function must be a number between 0 and 4. PERCENTILE(A1:A20,0.25) gives the exact same result. It’s up to you which function you would like to use for quartiles. Below you can find an overview.

Worked Example:   Calculate Conditional Percentile 'IF' in table in Excel
Minimum Value PERCENTILE(A1:A20,0) QUARTILE(A1:A20,0) MIN(A1:A20)
1st Quarter PERCENTILE(A1:A20,0.25) QUARTILE(A1:A20,1)
Median PERCENTILE(A1:A20,0.50) QUARTILE(A1:A20,2) MEDIAN(A1:A20)
3rd Quarter PERCENTILE(A1:A20,0.75) QUARTILE(A1:A20,3)
Maximum Value PERCENTILE(A1:A20,1) QUARTILE(A1:A20,4) MAX(A1:A20)

Post navigation

Previous Post:

Calculate Weighted Average in Excel

Next Post:

How to Create Box and Whisker Plot in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to create email address from name in Excel
  • How to check if cell contains number in Excel
  • Join Text Strings Using Concatenate or ‘& Operator’ in Excel
  • How to convert numbers to text using TEXT function in Excel
  • Remove text by variable position in a cell in Excel
  • How to determine year is a leap year in Excel
  • Get day name from date in Excel
  • Get project midpoint in Excel
  • Custom weekday abbreviation in Excel
  • WORKDAY function: Description, Usage, Syntax, Examples and Explanation
  • XNPV function: Description, Usage, Syntax, Examples and Explanation
  • EFFECT function: Description, Usage, Syntax, Examples and Explanation
  • PRICE function: Description, Usage, Syntax, Examples and Explanation
  • COUPNCD function: Description, Usage, Syntax, Examples and Explanation
  • PMT function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles