logo-v-light

Our Service

PERCENTILE.INC

In this comprehensive article, we will cover everything you need to know about the PERCENTILE.INC function in Excel. The PERCENTILE.INC function is a statistical function that calculates the value at a specific percentile within a given data set. This function is particularly useful when you need to analyze data distribution and identify trends or patterns in the data.

PERCENTILE.INC Syntax

The syntax for the PERCENTILE.INC function in Excel is as follows:

PERCENTILE.INC(array, k)

Where:

  • array – This is the range of cells containing the data set for which you want to calculate the percentile.
  • k – This is the percentile value you want to calculate, expressed as a decimal number between 0 and 1, inclusive. For example, if you want to calculate the 90th percentile, you would enter 0.9 as the value for k.

PERCENTILE.INC Examples

Let’s look at some examples of how to use the PERCENTILE.INC function in Excel:

Example 1: You have a data set of test scores for a class of 30 students, and you want to calculate the 75th percentile score. Assuming the test scores are in cells A1:A30, you would use the following formula:

=PERCENTILE.INC(A1:A30, 0.75)

This formula will return the test score at the 75th percentile for the given data set.

Example 2: You have a data set of monthly sales figures for a store, and you want to calculate the 90th percentile sales figure. Assuming the sales figures are in cells B1:B12, you would use the following formula:

=PERCENTILE.INC(B1:B12, 0.9)

This formula will return the sales figure at the 90th percentile for the given data set.

PERCENTILE.INC Tips & Tricks

Here are some tips and tricks to help you get the most out of the PERCENTILE.INC function in Excel:

  • Remember that the k value must be between 0 and 1, inclusive. If you enter a value outside of this range, Excel will return an error.
  • If you want to calculate the value at a specific percentage, simply divide the percentage by 100 to convert it to a decimal value. For example, if you want to calculate the value at the 80th percentile, you would enter 0.8 as the k value.
  • Keep in mind that the PERCENTILE.INC function will interpolate between values in the data set if the specified percentile falls between two data points. This means that the returned value may not be an exact match to any of the values in the data set.

Common Mistakes When Using PERCENTILE.INC

Here are some common mistakes to avoid when using the PERCENTILE.INC function in Excel:

  • Using an incorrect k value: Make sure to enter the k value as a decimal between 0 and 1, inclusive. Entering a value outside of this range will result in an error.
  • Not using an absolute cell reference for the array: If you plan to copy the formula to other cells, make sure to use absolute cell references for the array to avoid errors. For example, use $A$1:$A$30 instead of A1:A30.
  • Forgetting to sort the data set: While the PERCENTILE.INC function does not require the data set to be sorted, sorting the data can help you better visualize the distribution of values and identify trends or patterns in the data.

Why Isn’t My PERCENTILE.INC Working?

If you’re having trouble with the PERCENTILE.INC function in Excel, here are some common issues and their solutions:

  • Error message: If you see a #NUM! error, it’s likely because the k value is outside the valid range of 0 to 1, inclusive. Check your k value and make sure it’s within the valid range.
  • Incorrect result: If the result seems incorrect, double-check your array and k values to make sure they are correct. Also, ensure that you’re using the correct function (PERCENTILE.INC) and not a similar function like PERCENTILE.EXC, which calculates the value at a specific percentile using a different method.
  • Array not updating: If you’ve added new data to your data set and the result of the PERCENTILE.INC function hasn’t updated, make sure that the array in your formula includes the new data. Update the cell references in the formula if necessary.

PERCENTILE.INC: Related Formulae

Here are some related formulae that you might find useful when working with the PERCENTILE.INC function in Excel:

  • PERCENTILE.EXC: This function calculates the value at a specific percentile using a different method than PERCENTILE.INC. It is more appropriate for data sets with a large number of values.
  • QUARTILE.INC: This function calculates the value at a specific quartile (25th, 50th, or 75th percentile) within a given data set.
  • PERCENTRANK.INC: This function calculates the relative rank of a specific value within a data set, expressed as a percentage.
  • RANK.AVG: This function calculates the rank of a specific value within a data set, with the average rank returned for duplicate values.
  • RANK.EQ: This function calculates the rank of a specific value within a data set, with the same rank returned for duplicate values.

By understanding the PERCENTILE.INC function and its related formulae, you can effectively analyze data distribution and identify trends or patterns in your data sets. With this knowledge, you’ll be well-equipped to make data-driven decisions and improve your overall data analysis skills in Excel.

Related

Did you find this article useful?

Share it with your friends or colleagues

About Aepoch Advisors

We are a boutique accounting and consulting firm servicing international businesses operating in China. We offers book keeping and business advisory service. We also help our clients select and implement SAAS business applications. 
 
Cloud technology significantly reduces cost foreign companies traditionally spent on tax compliance and ERP systems. Our cloud professionals can help with streamlining your management and controling structure, as well as advising you on how to reduce risks and maximize profits with software purposely built for Chinese business. Contact us today to learn more about our services.
Learn More