CALCULATE MOST USED STATISTICS MEASURES IN EXCEL

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.

VARIANCE

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.

Formula For Calculating Variance

V = (Σ(x - μ)^2 / n)

Where:

• x is a data in set,
• μ is the mean,
• n is the number of values in set
• Σ is the sum of.

Population vs Sample in Variance

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))

Calculate Variance In Excel

In Microsoft Excel, you can use the VAR.S function to find the sample standard deviation of a dataset. The formula is:

=VAR.S(range)

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:

=VAR.S(A1:A10)

If you want to calculate the population variance, the formula is:

=VAR.P(range)

STANDARD DEVIATION

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 For Standard Deviation

Formula: SD = √(Σ(x - μ)^2 / n)

For:

• x is a data in set,
• μ is the mean,
• n is the number of values in set,
• Σ is the sum of

A Note: Population vs Sample in Standard Deviation

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))

Calculating of The Standard Deviation in Excel

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:

=STDEV.S(A1:A10)

If you want to calculate standard deviation for a population, the formula will be:

=STDEV.P(range)

CORRELATION

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.

Formula For Correlation

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)

where:

• X and Y are the two sets of data
• cov(X, Y) is the covariance between X and Y
• sX and sY are the standard deviation of X and Y

How to Calculate Correlation In Excel

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:

=CORREL(A1:A10, B1:B10)

Conclusion

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.