Rank and Percentile Calculations With Microsoft Excel

Search:

Rank and Percentile Calculations With Microsoft Excel

Excel includes six functions used for finding rank and percentile on values in a data set.

LARGE

Use the LARGE function to find the kth largest value in a data set. While you can use the maximum function to find the largest value in a data set, you can use the LARGE function to find the runner up or third-place value. The LARGE function uses the following syntax:

=LARGE (data set range, k)

SMALL

Use the SMALL function to find the kth smallest value in a data set. Although you can use the MIN function to find the smallest value in a data set, you can use the SMALL function to find multiple values at the bottom. The SMALL function uses the following syntax:

=SMALL (data set range, k)

RANK

To find the rank of a value in a data set relative to other values in the data set, you can use the RANK function. The RANK function uses the following syntax:

=RANK (number, data set, order)

where number is the number whose rank you want to find, data set is the list of values against which you want to rank it, and order tells Excel to rank in ascending or descending order. Enter a nonzero value to rank the numbers in ascending order. Enter zero or leave the order parameter blank to rank in descending order.

NOTE: If the data set includes duplicate values, Excel gives these values the same rank. This effects the ranks of subsequent numbers. For example, if the value 3 appears twice and has a rank of 6, then 4 would have a rank of 8, and no value would have a rank of 7.

PERCENTRANK

To find the rank of a value in a data set as a percentage, you can use the PERCENTRANK function. The PERCENTRANK function uses the following syntax:

=PERCENTRANK (data set, x, significance)

where x is the value whose rank you want to find, data set is the list of values against which you want to rank it, and significance tells Excel the number of significant digits it should use for the value. The value you get is between 0 and 1; you need to multiply by 100 to get the actual percent ranking.

PERCENTILE

Percentile is a measure that locates where a value stands in a data set. The kth percentile divides the data so that at least p percent are of this value or less and (100-p) percent are this value or more. If you have a set of data and need to find the value at a certain percentile, you use the PERCENTILE function in Excel. The PERCENTILE function uses the following syntax:

=PERCENTILE (data set range, k)

For example, if a score needs to be above the 80th percentile for admission, you can find which value defines that percentile by entering 0.8 for k.

QUARTILE

The QUARTILE function in Excel is closely related to the PERCENTILE function. People often use quartiles, which order the values in a data set into quarters, when dividing populations into groups based on sales and survey data. The first quartile is the 25th percentile. The second quartile is the median, or 50th percentile. The third quartile is the 75th percentile. The fourth quarter is the maximum value. The QUARTILE function uses the following syntax:

=QUARTILE (data set range, quartile)

For the quartile parameter, enter 0 for the minimum value, 1 for the first quartile, 2 for the second quartile, 3 for the third quartile, or 4 for the maximum value.

Article Source: http://articlenexus.com

Seattle accountant and bestselling computer book author Stephen L. Nelson wrote the MBA's Guide to Microsoft Excel, from which this short article is adapted. Nelson also writes and edits downloadable do-it-yourself kits that businesses and investors can use for setting up a New Hampshire limited liability company or a New Jersey limited liability company.

Please Rate this Article

Not yet Rated

Click the XML Icon Above to Receive Accounting Articles Via RSS!

Additional Articles From - Home | Accounting

	Print This Article
	Add To Favorites
	Email to Friends
	Ezine Ready

Submit Articles
Member Login
Top Authors
Most Popular Articles
Submission Guidelines
Ezine Notifications
Article RSS Feeds

(Your 160x160 ad here)
Only $50 a month-click here

Everything you need for your website and home business.
	Adsense Site Builder
	Adsense Analyzer Software
	Clip Art
	Domain Names
	Header Graphics
	Internet Business Info
	Photo Art
	SEO Software
	Web Hosting $4.95/mo
	Website Templates
	Web Site Promotion Tools
AdvancedWebsiteTools.com

Link Exchange
Blog by WordPress
Privacy Policy
Terms of Service
About Us
Contact Us
Banner Advertisements

Have you Victimized by a Business or the Government? File your consumer complaints or business complaints at BusinessReporter.net!

Structured Settlement?
Free information to help you learn more about structured settlements, your rights, and how to select the best company for you.

Affordable Discount Dental Plans
Choose from over 30 top-quality national and regional discount dental plans. Save 10% to 60% on most dental procedures

Hair Loss Remedies

Remove Bad Credit
Legally Erase Bad Credit and Improve Your Credit Rating
with Self Help Credit Repair
Do It Yourself Credit Repair

Free Diabetic Supplies

Antique Radios | Ham Radios