PERCENTAGE ,PERCENTILE AND WEIGHTED MEAN
Recall the steps to find Sum :
1. Select the cell where the answer needs to be displayed
2. Open function wizard
3. Select the function category and function name
4. Select the data
5. Click OK
Observe the above steps and calculate the sum of marks of all students given in Handout 1.
Following is the snapshot after having total of all marks of students.
PERCENTAGE
It is a user defined function which help in finding percentage . When we say “percent we really saying “per 100” . One percent (1%) means 1 per 100.
1% of this line is shaded green: it is very small isn't it?
We don't have any formula to apply it so we need to define the formula to calculate the percentage in the LibreOffice Calc.
Calculate Percentage :
If you want to find out that what is the percentage of the students of class . Use the spreadsheet given as handout 1 to perform the following steps :
1. Select the cell where you want the FORMULA to be displayed.
2. Use the SUM function and calculate the total of all marks obtained by single student.
3. Write the formula in all cells related to each student to get total of each student , try to make it in one column so that you can see it in proper order . This way you will have total of marks in one column (for eg. Column I)
4. Now again Select the cell where you want the FORMULA of percentage to be displayed.
5. Write the formula =cell number of marks obtained by student /sub total of marks *100 (for e.g. I4/500*100)
6. Now write the formula in all the cells i.e. for each student and the get percentage of each student (Refer Column J ).
PERCENTILE
A percentile (or a centile) is a measure used in statistics indicating the value below which a given percentage of observations in a group of observations fall. It Returns the alpha-percentile of data values in an array.
A percentile returns the scale value for a data series which goes from the smallest (Alpha=0) to the largest value (alpha=1) of a data series.
For Alpha = 25%, the percentile means the first quartile; Alpha = 50% is the MEDIAN.
Following is the way to use percentile function :
=PERCENTILE(Data; Alpha)
Where
Data represents the array of data.
Alpha represents the percentage of the scale between 0 and 1.
Example 1:
For example, suppose you have 25 test scores, and in order from lowest to highest they look like this: 43, 54, 56, 61, 62, 66, 68, 69, 69, 70, 71, 72, 77, 78, 79, 85, 87, 88, 89, 93, 95, 96, 98, 99, 99. To find the 90th percentile for these (ordered) scores, start by multiplying 90% times the total number of scores, which gives 90% ∗ 25 = 0.90 ∗ 25 = 22.5 (the index). Rounding up to the nearest whole number, you get 23.
Counting from left to right (from the smallest to the largest value in the data set), you go until you find the 23rd value in the data set. That value is 98, and it’s the 90th percentile for this data set.
Now say you want to find the 20th percentile. Start by taking 0.20 x 25 = 5 (the index); this is a whole number, so proceed from Step 3 to Step 4b, which tells you the 20th percentile is the average of the 5th and 6th values in the ordered data set (62 and 66). The 20th percentile then comes to (62 + 66) ÷ 2 = 64.
The median (the 50th percentile) for the test scores is the 13th score: 77.
This is one of method to calculate the percentile , but there are several other acceptable methods.
Example 2:
If you want to find the 80th percentile of students from data given in handout 1, follow the given steps:
1. Select the data from the cell no A3 to M13 .
2. Now select Column J and then goto to Format menu bar and select Sorting A.. Z tab. A dialog box will appear to ask a question regarding extend the selection , click on extend selection. (It will take data from A3 to M13).
SORT TAB ↓
3. See the marks and other details of students are arranged in ascending order.
4. Select the cell where you want the solution to be displayed and just before that cell write “80th percentile of this data set =”(this is just for your reference).
5. Goto Insert menu and select Function from drop down menu , a Function Wizard box will appear. Select the function ‘PERCENTILE’ under the category ‘STATISTICAL ’ in the function tab.
3. Type the name of function - PERCENTILE after the “=” sign in formula text box and select the range of cells or type the required range in range box of which the instances need to be counted (Eg: corresponding to percentage of students ) which has a numerical value.
4. Give your alpha value alpha section (for e.g 0.8 it is default alpha value which will represent 80% value to the scale ) and close the bracket.
5. Click OK and the percentile gets displayed on the selected cell .
WEIGHTED MEAN
A mean where some values contribute more than others. It is also called as weighted average. It returns the average of the absolute deviations of data points from their meanand displays the diffusion in a data set.
Following is the way to use percentile function :
AVEDEV(Number1; Number2; ...Number30)
Where
Number1, Number2,...Number30 are values or ranges that represent a sample. Each
number can also be replaced by a reference also like B3,B4,B5 etc.
Example
=AVEDEV(A1:A50)
Example 1:
If you want to find the weighted mean on the percentage or we can say average marks in all subjects from data given in handout 1, kindly follow the given steps:
1. Select the cell where you want the solution to be displayed.
2. Goto Insert menu and select Function from drop down menu , a Function Wizard box will appear. Select the ‘STATISTICAL ’ category under the category and search for function ‘AVEDEV’ in function list.
3. Type the name of function - AVEDEV after the “=” sign in formula text box and type open parenthesis after the name of function .
4. Now select the range of cells or type it parenthesis of which the instances need to be counted (Eg: corresponding to % of marks ) which has a numerical value.
OR
You can enter the individual values or their reference
In this example we are taking the whole range
5. Click OK and you will get the weighted mean in the desired cell.
Recall the steps to find Sum :
1. Select the cell where the answer needs to be displayed
2. Open function wizard
3. Select the function category and function name
4. Select the data
5. Click OK
Observe the above steps and calculate the sum of marks of all students given in Handout 1.
Following is the snapshot after having total of all marks of students.
PERCENTAGE
It is a user defined function which help in finding percentage . When we say “percent we really saying “per 100” . One percent (1%) means 1 per 100.
1% of this line is shaded green: it is very small isn't it?
We don't have any formula to apply it so we need to define the formula to calculate the percentage in the LibreOffice Calc.
Calculate Percentage :
If you want to find out that what is the percentage of the students of class . Use the spreadsheet given as handout 1 to perform the following steps :
1. Select the cell where you want the FORMULA to be displayed.
2. Use the SUM function and calculate the total of all marks obtained by single student.
3. Write the formula in all cells related to each student to get total of each student , try to make it in one column so that you can see it in proper order . This way you will have total of marks in one column (for eg. Column I)
4. Now again Select the cell where you want the FORMULA of percentage to be displayed.
5. Write the formula =cell number of marks obtained by student /sub total of marks *100 (for e.g. I4/500*100)
6. Now write the formula in all the cells i.e. for each student and the get percentage of each student (Refer Column J ).
PERCENTILE
A percentile (or a centile) is a measure used in statistics indicating the value below which a given percentage of observations in a group of observations fall. It Returns the alpha-percentile of data values in an array.
A percentile returns the scale value for a data series which goes from the smallest (Alpha=0) to the largest value (alpha=1) of a data series.
For Alpha = 25%, the percentile means the first quartile; Alpha = 50% is the MEDIAN.
Following is the way to use percentile function :
=PERCENTILE(Data; Alpha)
Where
Data represents the array of data.
Alpha represents the percentage of the scale between 0 and 1.
Example 1:
For example, suppose you have 25 test scores, and in order from lowest to highest they look like this: 43, 54, 56, 61, 62, 66, 68, 69, 69, 70, 71, 72, 77, 78, 79, 85, 87, 88, 89, 93, 95, 96, 98, 99, 99. To find the 90th percentile for these (ordered) scores, start by multiplying 90% times the total number of scores, which gives 90% ∗ 25 = 0.90 ∗ 25 = 22.5 (the index). Rounding up to the nearest whole number, you get 23.
Counting from left to right (from the smallest to the largest value in the data set), you go until you find the 23rd value in the data set. That value is 98, and it’s the 90th percentile for this data set.
Now say you want to find the 20th percentile. Start by taking 0.20 x 25 = 5 (the index); this is a whole number, so proceed from Step 3 to Step 4b, which tells you the 20th percentile is the average of the 5th and 6th values in the ordered data set (62 and 66). The 20th percentile then comes to (62 + 66) ÷ 2 = 64.
The median (the 50th percentile) for the test scores is the 13th score: 77.
This is one of method to calculate the percentile , but there are several other acceptable methods.
Example 2:
If you want to find the 80th percentile of students from data given in handout 1, follow the given steps:
1. Select the data from the cell no A3 to M13 .
2. Now select Column J and then goto to Format menu bar and select Sorting A.. Z tab. A dialog box will appear to ask a question regarding extend the selection , click on extend selection. (It will take data from A3 to M13).
SORT TAB ↓
3. See the marks and other details of students are arranged in ascending order.
4. Select the cell where you want the solution to be displayed and just before that cell write “80th percentile of this data set =”(this is just for your reference).
5. Goto Insert menu and select Function from drop down menu , a Function Wizard box will appear. Select the function ‘PERCENTILE’ under the category ‘STATISTICAL ’ in the function tab.
3. Type the name of function - PERCENTILE after the “=” sign in formula text box and select the range of cells or type the required range in range box of which the instances need to be counted (Eg: corresponding to percentage of students ) which has a numerical value.
4. Give your alpha value alpha section (for e.g 0.8 it is default alpha value which will represent 80% value to the scale ) and close the bracket.
5. Click OK and the percentile gets displayed on the selected cell .
WEIGHTED MEAN
A mean where some values contribute more than others. It is also called as weighted average. It returns the average of the absolute deviations of data points from their meanand displays the diffusion in a data set.
Following is the way to use percentile function :
AVEDEV(Number1; Number2; ...Number30)
Where
Number1, Number2,...Number30 are values or ranges that represent a sample. Each
number can also be replaced by a reference also like B3,B4,B5 etc.
Example
=AVEDEV(A1:A50)
Example 1:
If you want to find the weighted mean on the percentage or we can say average marks in all subjects from data given in handout 1, kindly follow the given steps:
1. Select the cell where you want the solution to be displayed.
2. Goto Insert menu and select Function from drop down menu , a Function Wizard box will appear. Select the ‘STATISTICAL ’ category under the category and search for function ‘AVEDEV’ in function list.
3. Type the name of function - AVEDEV after the “=” sign in formula text box and type open parenthesis after the name of function .
4. Now select the range of cells or type it parenthesis of which the instances need to be counted (Eg: corresponding to % of marks ) which has a numerical value.
OR
You can enter the individual values or their reference
In this example we are taking the whole range
5. Click OK and you will get the weighted mean in the desired cell.
No comments:
Post a Comment