Excel is a computer program developed by Microsoft Corporation that allows users to create, edit, and analyze data using spreadsheets. It is commonly used for organizing and manipulating data, performing calculations, and creating charts and graphs for visualizing data.
In this article, we will talk about briefly most used statistics measures in different fields and their calculations in excel. Let's get started.
The first measure is variance. Variance is a statistical tool used to evaluate the dispersion of data in a set. It gives information about how much the individual data points differ from the mean of the set.
V = (Σ(x - μ)^2 / n)
Due to the difficulty in obtaining data for the entire population, a sample is taken and the variance is calculated. As a result, there will be a slight modification in the formula, as follows:
V = (Σ(x - μ)^2 / (n-1))
In Microsoft Excel, you can use the VAR.S function to find the sample standard deviation of a dataset. The formula is:
Where range represents the group of values for which you want to determine the variance. For example, if your data is located in the range A1:A10, the formula to calculate the variance would be:
If you want to calculate the population variance, the formula is:
The standard deviation is a commonly used statistical measure that quantifies the spread or variability of a data set. It is utilized across a variety of fields, including finance, engineering, and others.
Formula: SD = √(Σ(x - μ)^2 / n)
As it can be difficult to obtain data for the entire population, a sample is selected and the standard deviation is calculated, which requires a small change in the formula, as shown below:
SD = √(Σ(x - μ)^2 / (n-1))
In Microsoft Excel, you can use the STDEV.S(range) function to find the sample standard deviation of a data set.
Range represents the group of values for which you want to determine the standard deviation. For example, if your data is located in the range A1:A10, the formula to calculate the standard deviation will be:
If you want to calculate standard deviation for a population, the formula will be:
Correlation is a statistical tool that measures the strength and direction of the relationship between two variables. This tool is used to determine if changes in one variable are associated with changes in another variable.
Before showing calculating correlation in excel let's see matmaticsel formula for calculating it. The formula for the Pearson's correlation coefficient (also known as the Pearson's r) is:
r = cov(X, Y) / (sX * sY)
Fortunately, we don't need to calculate each steps to reach result because excel has a function called CORREL(array1, array2)
Where array1 is the first set of data and array2 is the second set of data.
For example, if you have two columns of data, A and B with values ranging from 1 to 10, you can calculate the correlation coefficient between the two columns by using the following formula:
In this guide, we've talked about the calculations of the most used statistical measures in wide range of field from economy to psychology in excel which is mostly used tool for manipulating data.
Thank you for reading.