Can someone explain the percentile function

drag-driver

Board Regular
Joined
Feb 18, 2002
Messages
91
Dont know what it does and microsoft help doesnt help much. An example of how it calulates and whta its used for would be great

Thanks for the help
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Percentile is not Excel's most-oftenly used function, so to anyone reading this, please chime in with where my understanding of it is incorrect.

As you know, a percentile is a relative standing. It describes where a number (such as a student's test score, or a company's price earnings ratio) ranks among other students' test scores, or other companies' price earning ratios.

The percentile function confuses some people, because it does not return a percentile rank. Instead, it returns the value of what a given percentile rank would be.

Why would someone want to know what the Percentile rank would return? Usually, in order to set (or know ahead of time) a threshold of acceptability or rejection of a given percentile's rank among a range of numbers (such as test scores or PERs).

Here's an example of how the Percentile function can be used, taken right out of Patrick Blattner's book, "Microsoft 2000 Functions in Practice" (which in my opinion, ranks in the 80th percentile of Excel books...I've read worse, but not bad):

Range D5:D16 contains the price earnings ratios of 12 different companies. Those ratio numbers are (starting in D5) 7, 16, 17, 18, 19, 19, 20, 22, 31, 34, 38, 60.

In F6 is a percentile number that you enter. Let's say you enter the number 0.7, to represent 70%.

The syntax is PERCENTILE(Range, xValue)

In G6 you enter the formula
=PERCENTILE(D5:D16,F6).
This would return the number 28.3

What G6 now demonstrates is that the price earnings ratio of 70% of the companies are less than 28.3, and the other 30% of the companies have a price earnings ratio of greater than 28.3.

Hope this helps.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top