Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.
Here we go through everything that you need to know to better understand the STANDARDIZE function, starting with understanding what a standardized value is and how we can use it.
What Is the Standardized Value of a Distribution?
When one standardizes (normalizes) a value from a distribution the intention is to adjust a value of a probability distribution into alignment. When test scores are standardized, the intention would be to align the distribution of scores of students in the class to a normal distribution.
Standardized scores are useful because they allow one to find the probability of a score occurring and the ability to compare two scores from different normal distributions.
The Purpose of the STANDARDIZE Function
The standardize function is used to return a normalized value from a distribution as stated earlier. The normalized value that is returned is also called a Z-score. The Z-score is essentially the number of standard deviations from the population mean for a data point.
The formula in the illustration below shows exactly how the STANDARDIZE function calculates a z-score. The mean is subtracted from the number to be standardized. Then, this value is divided by the standard deviation of the data set.
STANDARDIZE Function Syntax
The STANDARDIZE function needs to be inputted like a formula in Excel. To manually add this formula to a cell, the following steps should be taken:
1. A cell need to be clicked
2. "=STANDARDIZE(" needs to be typed into the cell.
3. An x value must be added followed by a comma.
4. The mean is added followed by a comma.
5. The standard standard deviation is added followed by a comma.
6. Finally, a close parenthesis is used at the end and the function can be entered.
The syntax is shown below followed by details on each arguments that makes up the STANDARDIZE function.
=STANDARDIZE(x, mean, standard_dev)
X - This is required and also the value that is to be normalized.
Mean - The mean is required and better described as the arithmetic mean of the data set.
Standard_dev - Required. The standard deviation of the data set.
Inserting the STANDARDIZE Function
The STANDARDIZE function can also be inserted into an Excel spreadsheet. This method is meant for those who are unfamiliar with the function syntax. The first steps to inserting start below.
To use this method the cell where the standardized result must display is selected. Next, the formulas tab is selected and the "More Functions" button on the Excel ribbon needs to be selected. The statistical selection from the list is then chosen followed by the STANDARDIZE selection from the drop down menu.
Steps to Insert the Standardize Function
After the the functions arguments window appears, values or references for each argument can be added to those fields.
The arrows on the side of the field can be used as an aid to selecting a cell reference. After the reference is selected or values are entered the OK button can be clicked.
The Functional Arguments
STANDARDIZE Function Syntax Examples
Below are some examples with explanations of types of syntax that can be used in two examples.
Used with cell references - Here the standardized form can be found with cell references. Each reference represents data calculated from the same distribution where A2 represents x or the value being standardized, A3 represents the mean, and A4 represents the standard deviation.
Used with numbers - Here the standardized form can be found directly with numbers added to the standardize formula. The first number 15 is the x, 14.2 is the mean, and 1.7 would be the standard deviation.
Microsoft. (n.d.). STANDARDIZE function. Retrieved January 5, 2020, from https://support.office.com/en-us/article/standardize-function-81d66554-2d54-40ec-ba83-6437108ee775.
Wikipedia. (2019, December 5). Normalization (statistics). Retrieved January 24, 2020, from https://en.wikipedia.org/wiki/Normalization_(statistics)
- Converting Measurement Units: Using the Convert Function in Microsoft Excel
Describes how to use the CONVERT function within Microsoft Excel to convert various types of measurement units to other types of units. Types of conversions include: weight and mass, distance, time, pressure, force, energy , power, magnetism, tempera
- How to Use the VLOOKUP Function in Excel
Covers how to use the VLOOKUP function in Microsoft Excel with examples on the correct syntax to use.
- How to Remove Errors in Excel With the IFERROR Function
Shows a Microsoft Excel user how to apply the IFERROR Function to remove errors in cells.
- How to Use the ABS Function in Excel
Covers how to use the ABS function in Microsoft Excel with examples on the correct syntax to use.
- How to Use the AVERAGE Function in Excel
Shows how to use the AVERAGE function in Excel with application examples.
- How to Use the COUNT Function in Excel
Shows a Microsoft Excel user how to use the count function in a spreadsheet.
To learn more about using functions in Excel I recommend purchasing Excel Formulas & Functions For Dummies.
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.
© 2020 Joshua Crowder