As an analyst, you must have encountered the concept of data normalization many times while working. This grave concept demands recognizing its importance for the success of an analyst. Therefore, you need to learn how to normalize data Excel for successful project completion.
What is Normalization?
Normalization is often considered as standardization, in which you can transform data based on the mean and standard deviation for the complete dataset. This transformed data simply refers to a standard distribution with a mean of 0 and variance of 1.
The values you see above the mean are positive values, whereas the values that appear below the mean are negative values.
Normalizing Data in Excel:
Standardized is a function that lets you calculate and shows the normalized value from a distribution that arithmetic mean and standard deviation describes. For this function, you need to have the arithmetic mean and standard deviation values from the user.
STANDARDIZE(x, mean, standard_dev)
When you need to find the arithmetic mean of a dataset, you can simply use the AVERAGE function.
AVERAGE(number1,[number2],…)
To find the standard deviation of a dataset, you can avail of two options based on the size of your data:
STDEV.P: STDEV.P(number1,[number2],…)//It is used for whole population
STDEV.S: STDEV.S(number1,[number2],…)// It is used for a sample of data
How to Normalize Data Excel:
The process of finding standardization is not much tricky. Suppose that you need to find a list of values from y to z that starts with the number X.
X: It is the first value that appears in the list
The formula that lets you find the standardized value X is:
X_standardized = (X – mean of range)/ standard deviation of the range
Below is the Excel formula you can use for this calculation is:
=STANDARDIZE (X; mean of range; standard deviation of the range)
For this formula, you would have to find the mean and standard deviation formulas.
The formula for Mean calculating is = AVERAGE(range of values)
The formula for Standard Deviation is =STDEV(range of values)
Now, let’s understand these formulas with an example:
First, you need to find the mean value. For this, you can use the following formula:
=AVERAGE(range of the values)
Next, you need to find the standard deviation. For this, you can use the following formula:
=STDEV(range of value)
Since we have everything we need to find the normalized data, let’s find it out by following the below-given formula:
=STANDARDIZE(X, mean of range, standard deviation of the range)
Final Words:
Normalization or standardization functions are equally important when you are assigned to make a presentation on a specific subject or else when you need to minimize the data size to make deductions. Both functions are used for calculating the mean and standard deviation and each function has its formula that you can use.
Excel as you know is an extensive tool for analyzing datasets of multiple kinds that’s why advancement has been made in this software to perform functions of any kind.
