Statistical Analysis with Excel-Chapter10
We finished
Measures of central tendency
Measures of dispersion
Now we will go through Measures of Position:
Rank and Percentiles.
We had mean, then why median, we had Measures of central tendency(mean, median, mode) then why variance, we had Variance then why standard deviation. Finally, we have a standard deviation then why Rank.
standard deviation is used to measure data value’s relative standing to other values in the group. but if you do not want to use it we have an alternative i.e Ranking.
It determines each value’s rank within the group.
Two functions available:
RANK.EQ and RANK.AVG
The only difference is based on how they treat ties.Rank.EQ assigns same rank to all the scores that are tied. But the RankAvg takes the average of the ranks those tied value gets and then gives them the ranking.
The Rank formulas are used to find rank for one value and then copy that to get for other values.
The data used her is same from Ukraine-Russia war.
LARGE and SMALL
these are two functions that help you find the value when you give the rank.
Percentiles:
Closely like Rank, we have percentile which gives each score standing in the group as the percent of scores below it.
PERCENTILE.INC and PERCENTILE.EXC
the first formula works with “greater than or equal to” and the second one works with ‘greater than.
We will use a very informative dataset from Kaggle.
Load the data that is the train set and then convert to pivot table.Take only two fields house Id and sold price.Also, filter the id and take ≤20.then sort the values by descending order of Sold price.
Although I calculated all the percentiles from 95th to the percentiles but most often used are 25th,50th,75th and 100the pecentiles.
These percentiles are called as Quartiles.
QUARTILE.INC and QUARTILE.EXC are the Excel formulas to get that.
In previous percentile formulas you gave the percentile and the score of it was given.In the next two formulas you choose the score and get the percentile of the score.
PERCENTILERANK.INC
PERCENTILERANK.EXC
Lastly We will cover the most important tool,Data Analysis tool ,that provides with a list of important test and techniques used by Analysts.
Data tab-Analysis-Data Analysis-Rank And percentile
