Adding a horizontal
line to a chart
| There are many instances when one needs
to add a visual indicator to a chart. This could be a line
indicating a goal of some sort. Or, it could be a line
showing the average of the graphed data. In TQM control
charts, it could be a line so-many standard deviations from the
mean. One way draw
such a line would be to calculate the values in cells in
a worksheet, and plot these cells on the chart.
Another way is to use a named formula, which means that
the charted threshold values do not show
up on the worksheet. This tutorial shows how to
use a named formula. |
 |
| The example uses the data set on the
right. Of course, you will use your own data. |
 |
| First, create the
chart from the data set. The example uses a XY
Scatter chart. |
 |
| Next, define two
names. The first, aRng, is just for convenience.
The second, meanLine, is the one that generates as many
numbers as in the original data, each equal to the
average.
How? |
 How
does the meanLine named formula work?
(a) OFFSET(aRng, 0,1) selects the 2nd column of data,
i.e., the y-values.
(b) AVERAGE(...) gives the average of y-values.
(c) ROW(aRng)/ROW(aRng) creates an array of ones.
The array has as many data points as in the original
data.
Multiplying (b) by (d) replaces the ones in the array by
the average value. |
| Next, add a new
series to the chart, using this newly defined name.
How? |
 |
| Format the new
series to hide the markers and show the line.
The job is done! |
|
|
