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.
Table of Contents
1. Introduction
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. The NORM.DIST function lets you create a bell-shaped distribution.
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?
The shape of the normalized distribution allows you to determine
- the statistical method to use
- identify potential issues with the data (outliers, skewness, kurtosis etc)
- compare distributions across different groups or conditions
2. STANDARDIZE function Syntax
STANDARDIZE(x, mean, standard_dev)
3. 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. |
4. STANDARDIZE Function Example 1
A manufacturing company produces bolts with a target diameter of 10 mm. The process has a mean of 10.2 mm and a standard deviation of 0.3 mm. If a bolt has a diameter of 9.8 mm what is its standardized value?
The arguments are:
- x = 9.8 mm
- mean = 10.2 mm
- standard_dev = 0.3 mm
These arguments are specified in cells C13,C14, and C15 respectively, in the image above.
The image above shows a chart containing a blue curve representing the probability mass function of a normal distribution where the mean is 0 and the standard deviation is 1. The black lines represents the intersection between the standardized value and the blue curve which is the normal distribution.
Formula in cell C17:
The formula returns -1.333333 which represents the standardized x value, you can find that value on the chart above.
In the image above, locate the value 9.8 on the x-axis. From that point, draw an imaginary vertical line upwards until it intersects with the blue curve, which represents the probability mass function for a normal distribution. Then, follow the point of intersection horizontally towards the y-axis to the left. You will find that the corresponding value on the y-axis is approximately 0.164.
You can use the PHI function to calculate the y value:
returns approx. 0.164
5. STANDARDIZE Function Example 2
In a study of reaction times the mean response time was 250 milliseconds with a standard deviation of 30 milliseconds. If a participant had a reaction time of 260 milliseconds, what was their standardized reaction time?
The arguments are:
- x = 260 ms
- mean = 250 ms
- standard_dev = 30 ms
These arguments are specified in cells C13,C14, and C15 respectively, in the image above.
The image above shows a chart containing a blue curve representing the probability mass function of a normal distribution where the mean is 0 and the standard deviation is 1. The black lines represents the intersection between the standardized value and the blue curve which is the normal distribution.
Formula in cell C17:
The formula returns 0.333333 which represents the standardized x value, you can find that value on the chart above.
In the image above, locate the value 0.333333 on the x-axis. From that point, draw an imaginary vertical line upwards until it intersects with the blue curve, which represents the probability mass function for a normal distribution. Then, follow the point of intersection horizontally towards the y-axis to the left. You will find that the corresponding value on the y-axis is approximately 0.377.
You can use the PHI function to calculate the y value:
returns approx. 0.377
6. STANDARDIZE Function Example 3
In a class of 50 students the final exam scores were recorded. The mean score was 75 and the standard deviation was 10. If a student scored 85 on the exam what was their standardized score?
The arguments are:
- x = 85
- mean = 75
- standard_dev = 10
These arguments are specified in cells C13,C14, and C15 respectively, in the image above.
The image above shows a chart containing a blue curve representing the probability mass function of a normal distribution where the mean is 0 and the standard deviation is 1. The black lines represents the intersection between the standardized value and the blue curve which is the normal distribution.
Formula in cell C17:
The formula returns 1 which represents the standardized x value, you can find that value on the chart above.
In the image above, locate the value 1 on the x-axis. From that point, draw an imaginary vertical line upwards until it intersects with the blue curve, which represents the probability mass function for a normal distribution. Then, follow the point of intersection horizontally towards the y-axis to the left. You will find that the corresponding value on the y-axis is approximately 0.24.
You can use the PHI function to calculate the y value:
returns approx. 0.24
7. STANDARDIZE function not working
STANDARDIZE returns a #NUM! error if argument standard_dev ≤ 0
8. 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 73 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