How to use the STANDARDIZE function
What is the STANDARDIZE function?
The STANDARDIZE function calculates a normalized value from a distribution characterized by mean and standard_dev.
What is a distribution?
A distribution describes how data is distributed across possible values. It shows the frequency of values.
What is the mean of a distribution?
The average value (arithmetic mean) of a distribution, calculated by summing numbers and dividing by the count.
The AVERAGE function calculates the arithmetic mean.
What is the standard deviation of a distribution?
Standard deviation is a measure of dispersion that indicates how spread out the values in a dataset are from the mean. It is represented by the Greek letter sigma (σ).
The formula for calculating standard deviation is:
σ = √Σ(x - μ)2 / (N - 1)
Where:
σ = Standard deviation
Σ = Sum of
x = Values in the dataset
μ = Mean of the dataset
N = Number of values in the dataset
(N - 1) = Sample correction factor
The STDEV.P and STDEV.S functions calculate the standard deviation.
What is normalizing a distribution?
Normalizing a distribution means rescaling it to have a arithmetic mean of 0 (zero) and standard deviation of 1. See below on how STANDARDIZE function is calculated.
What is a normal distribution?
A symmetrical bell-shaped distribution where data clusters around the mean. Defined by its mean and standard deviation.
What is a standard normal distribution?
A standard normal distribution is a normal distribution with the mean of 0 (zero) and the standard deviation is 1.
Why normalize a distribution?
Normalizing a distribution standardizes the scale, this allows comparing different distributions on different scales. Normalizing puts different distributions on a common scale with a mean of 0 and standard deviation of 1. This allows direct comparisons of datasets that were measured on different scales.
Normalizing removes differences in variability and centers the distribution at 0. This isolates just the shape for comparison. Normalized, standardized data is easier to model and visualize compared to highly variable raw data. Normalizing a distribution is a fundamental data processing step.
STANDARDIZE function Syntax
STANDARDIZE(x, mean, standard_dev)
STANDARDIZE function Arguments
| x | Required. The value you want to normalize. |
| Mean | Required. The arithmetic mean of the distribution. |
| Standard_dev | Required. The standard deviation of the distribution. |
STANDARDIZE function example
Cell range B3:B28 contains random integers between 131 and 188. The standard deviation is calculated in cell E17:
The arithmetic mean is calculated in cell E18, we need the mean and standard deviation to standardize these random values in B3:B28.
The values in B3:B28 are put into different groups from 135 to 190 with an increment of 5. The top chart shows the count for each group (yellow line).
Formula in cell G3:
Cells G3:G28 contains standardized values based on B3:B28 and the std dev calculated in cell E17 and mean calculated in cell E18.
Standardized values mean that the std dev is 1 and the mean is 0 (zero), these calculations are made in cells J17:J18 to makes sure this is right. The bottom chart (blue line) shows the standardized values.
STANDARDIZE function not working
STANDARDIZE returns a #NUM! error if argument standard_dev ≤ 0
How is the STANDARDIZE function calculated?
You can standardize any normal distribution like this:
z = (x - µ)/σ
z = z-score
µ is the mean.
σ is the standard deviation.
Functions in 'Statistical' category
The STANDARDIZE function function is one of 74 functions in the 'Statistical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form