Normalized data refers to any number that is part of a distributed set of numbers that has been adjusted for the mean, or average, of that set and the set's standard deviation.
In Microsoft Excel, you can use the STANDARDIZE formula to normalize any number, provided you also have the average of its set and the standard deviation.
Open a new Excel workbook. Enter a set of data in column A, beginning at cell A2. Type a name for the data in cell A1.
Type Average below the data in column A. Beside the word Average in Column B, type =AVERAGE(). Place the cursor between the parenthesis and highlight the data in column A. Press Enter to calculate the formula. In the screenshot example, the formula is: =AVERAGE(A2:A13).
Video of the Day
Type Standard Deviation below the word Average. Tab over to column B and type =STDEV(). Again, place the cursor between the parenthesis, highlight the data cells and press Enter. In the screenshot example, the formula is: =STDEV(A2:A13).
Place the cursor in cell B2, beside the first value in column A. Type =STANDARDIZE() in the cell and place the cursor between the parenthesis. Type three cell coordinates:
- Type A1 for the cell being normalized, then a comma.
- Type B$ and then the cell number containing the AVERAGE formula, such as B$15, followed by a comma.
- Type B$ and the cell number containing the STDEV formula, such as B$16.
Press Enter to calculate the formula, which should look like this: =STANDARDIZE(A2,B$15,B$16).
Press Ctrl-C to copy cell B2. Highlight all the cells in B2 that are beside a value in the first column and press Ctrl-V to paste the formula into these cells. Now every value in column A has a normalized value in column B.