How to calculate Quartile in Excel

[Pages:5]How to calculate Quartile in Excel

Step1 In order to calculate Quartile, first we need to know what is Quartile and in

what condition we need to use Quartile.

Quartiles often are used in sales and survey data to divide populations into groups. For example, you can use QUARTILE to find the top 25 percent of incomes in a population.

QUARTILE(array,quart)

Array is the array or cell range of numeric values for which you want the quartile value.

Quart indicates which value to return.

IF QUART EQUALS 0 1 2 3 4

QUARTILE RETURNS Minimum value

First quartile (25th percentile) Median value (50th percentile) Third quartile (75th percentile)

Maximum value

Step2 For example we have the following data, and we want to find all five

quartile value for each province's population percentage. First thing we need to do is calculate the percentage. The percentage for each province's population is to use its population number divide by Canada's total population, then times 100. When you put this equation into excel it should like this: (In computer langue "PLUS" is "+", "MINUS" is "-", "MULTIPLY" is "*", and "DIVIDE" is "/").

Press Enter, then you will get the percentage number for Ontario. But now the number is not exactly what we want it looks like.

We just need couple click to fix it.

Under HOME tab, click the percentage sign (%), now you can see the number become a percentage number, but with no decimal, just click here. Click two or three time, so we can get two or three decimals. Now you should have a percentage number that we want.

After you get the percentage number for the first province, you don't want to do the same procedure again and again for every province, so we can just click on the black dot oh the right bottom corner of the first percentage number cell, which is here, then drag it down to the last cell of the percentage number going to be. And you should get all the percentage number for every province.

Step3 Now we can go sort the percentage numbers from largest to smallest

(descending order). Just select the provinces' name, the population and the percentage number. Then go to the HOME tab, on the right side you will see SORT & FILTER, click on the little arrow, then Custom Sort. In this case we want to sort the percentage number, so we will set the "Sort by" to Column D, where the percentage numbers are. Then we want it sort on Values, and from Largest to Smallest. After you click OK your date will be like this:

Step4 Then we just use the Quartile Formula mentioned in Step 1, and calculated

all four Quartile values (doesn't matter which cell you use now, we will move it later).

After you get all four Quartile values just find the same value in the percentage column, and then move the Quartile value right on the side on it. And one last thing is to color them in descending colors, so you can clearly see the number is decreasing or increasing. Use the same color but use a darker color for higher value. Your finished work should looks like this:

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download