Practical 2.
Quartiles, Deciles and Percentiles
1. For the following data giving the distribution of workers according
to income, calculate:
a. Third Quartile
b. Fourth Decile
c. 37th Percentile
Income 2000- 3000- 4000- 5000- 6000- 7000- 8000-
in Rs 3000 4000 5000 6000 7000 8000 9000
No of 55 78 101 162 97 63 44
workers
To calculate the third quartile, fourth decile, and 37th percentile for the
given data in Excel, you can follow these steps:
1. Enter the data into Excel:
Enter the income ranges in one column (let's say column A).
Enter the corresponding number of workers in another column
(let's say column B).
2. Calculate cumulative frequencies:
In an empty column, let's say column C, calculate the cumulative
frequencies.
In cell C2, enter the formula =B2.
In cell C3, enter the formula =C2+B3.
Drag this formula down to apply it to all rows.
3. Calculate total number of workers:
In an empty cell, let's say D1, calculate the total number of workers
using the SUM function. Enter the formula =SUM(B2:B8).
4. Calculate third quartile:
Third quartile is the 75th percentile. You can find it using the
PERCENTILE function.
In an empty cell, let's say D2, enter the formula
=[Link](A2:A8, 0.75, C2:C8).
A2:A8 is the range of incomes.
C2:C8 is the corresponding cumulative frequency range.
5. Calculate fourth decile:
Fourth decile is the 40th percentile. You can find it using the
PERCENTILE function.
In an empty cell, let's say D3, enter the formula
=[Link](A2:A8, 0.4, C2:C8).
A2:A8 is the range of incomes.
C2:C8 is the corresponding cumulative frequency range.
6. Calculate 37th percentile:
In an empty cell, let's say D4, enter the formula
=[Link](A2:A8, 0.37, C2:C8).
A2:A8 is the range of incomes.
C2:C8 is the corresponding cumulative frequency range.
After completing these steps, cells D2, D3, and D4 will contain the third
quartile, fourth decile, and 37th percentile respectively for the given data.
2. For the above data in question (1) draw a less than cumulative
frequency curve and a more than cumulative frequency curve on
the same graph and hence locate the median.
To draw a less than cumulative frequency curve and a more than
cumulative frequency curve on the same graph in Excel and locate the
median, you can follow these steps:
1. Prepare the data:
Create a new Excel worksheet.
Enter the income ranges in one column (let's say column A).
Enter the corresponding number of workers in another column
(let's say column B).
2. Calculate cumulative frequencies:
In an empty column, let's say column C, calculate the less than
cumulative frequency.
In cell C2, enter the formula =SUM($B$2:B2).
Drag this formula down to apply it to all rows.
3. Calculate more than cumulative frequencies:
In an empty column, let's say column D, calculate the more than
cumulative frequency.
In cell D2, enter the formula =SUM($B$2:$B$8)-C2.
Drag this formula down to apply it to all rows.
4. Create a chart:
Select the data in columns A, C, and D.
Go to the "Insert" tab on the Excel ribbon.
Choose the type of chart you want to create (line chart is
recommended).
5. Customize the chart:
Double-click on any part of the chart to open the "Format Chart
Area" pane.
Adjust the formatting as desired, such as adding titles, axes labels,
etc.
6. Locate the median:
The median can be located where the less than cumulative
frequency curve intersects the more than cumulative frequency
curve.
To find the median value, you can either visually locate the
intersection point on the chart or use Excel's tools for finding
intersections.
Alternatively, you can calculate the median directly from the data
by finding the income range where the cumulative frequency
crosses half of the total number of workers.
After completing these steps, you'll have a chart displaying both the less
than and more than cumulative frequency curves, and you can locate the
median from the intersection point or directly from the data.