A normalized curve is a representation of a data set on a graph. The
values of the data have all been brought in line with average value, so
the results can be plotted as a smooth curve, rather than a jagged mess.
This is done using the mathematical tools of mean averaging and
standard deviation. It's possible to set these functions up in an Excel
sheet, so they can be applied to any data set.
Instructions
-
-
1
Create a new workbook in Excel,
and ensure that there are at least two sheets within it. One of the
sheets will be for storing your data, and the second will be used for
performing the standard deviation calculations. Name the sheets by
right-clicking on the tabs at the bottom of the screen.
-
2
Make a list of the following
labels, which will be used as shorthand for the average values when
creating your curve: Mean, StdDev, NumRows, Zmin and Zmax. These labels
will store the values used in the formulas.
-
3
Select all the labels and the
fields next to them where the values will be stored. From the "Insert"
menu, choose "Name > Create." In the resulting window, check the left
column and un-check the right. This allows you to use the label names
in your formulas, rather than the cell locations.
-
4
Below the labels, create fields
for CurveMin and CurveMax. These will be used to store the actual
formulas. Repeat the labeling process described above.
-
5
In the field next to "CurveMin,"
enter "=Zmin*StdDev+Mean" minus the quotes. This will create the left
side of the normalized curve. Next to "CurveMax," enter the same
formula, this will create the right side of the curve.
-
6
Switch to the "Data" worksheet
and enter the values of your data. Use the "Sort A-Z" function to place
your data in consecutive order, from the lowest to the highest value. In
the first data field, which is the smallest number, add the following:
"=Zmin" to assign the label to the lowest value in your data set. Scroll
down to the largest number at the bottom of the list, and type in
"=Zmax."
-
7
Count the number of rows in your
data sheet and switch back to the formula sheet. Type the number of
rows into the "NumRows" field. This is required for working out the mean
average.
-
8
Add a "Total" field and the
bottom of your data sheet and insert the formula "sum(A1:A50)" where the
A1 and A50 values are are the cell addresses of your top and bottom
values. This will add up all the data values together. In the formula
sheet, type the formula "=A51/NumRows" where A51 is the cell address of
your "sum" formula on the data sheet. The "Mean" and "StdDev" fields in
the formula sheet should update automatically, leaving all the values
filled in.
-
9
Click the "Insert" menu followed
by "Chart." This will launch the Chart Wizard. In the "Chart Sub Type"
choose "Scatter chart created by smooth lines without markers." The
chart will automatically generate and appear, allowing you to add the
data in your chosen format. The line will represent the distribution of
values and their distances from the mean average, via a smooth,
normalized curve.
