How to Find Upper and Lower Quintiles in Excel

A series of numbers, such as sales and expenses, can demonstrate whether certain parts of your business are expanding or contracting -- but only if you can consolidate them into meaningful figures. For example, an increase in the sales of products that cost more, and a decrease in lower-priced goods may indicate that your business is growing its luxury market and getting smaller with those on a budget. Quintiles can help you to understand this information by showing you the upper and lower 20 percent of a range. Excel provides a built-in function for finding quintiles.

Enter the range of numbers, with one cell per number, in your Excel spreadsheet. If you already have a spreadsheet with numbers, skip this step.

Click in the cell where you want to show the upper quintile. Enter the following formula “=PERCENTILE.INC(“ -- without quotation marks here and throughout -- and only use the left parenthesis.

Click on the first cell of the range of numbers you entered earlier and drag the selection rectangle to the last cell of the range. The formula automatically fills in with a reference to the range. For example, if you entered the range in cells A1 to A8, the formula fills in as follows “=PERCENTILE.INC(A1:A8”.

Add a comma and “.8” after the formula and then close it with a right parenthesis so that the formula would look something like the following “=PERCENTILE.INC(A1:A8,.8)”. Press “Enter” to display the result in the formula cell.

Click in the cell where you want to show the lower quintile and repeat the process of entering the formula and selecting a range. However, replace the “.8” with a “.2” so your formula looks something like this “=PERCENTILE.INC(A1:A8,.2)”. Press “Enter” to display the result in the formula cell.