Adding an Average Line to a Chart Using Defined Names

Posted on September 04, 2007

Please Note: This technique appears not to work in Excel 2007 (SP 1). For some reason, not explained by Microsoft as far as I can tell, some (but not all) named ranges are no longer allowed in the Series() function. This appears to be yet another problem with the new charting engine. So, this post only applies to Excel 2003 and earlier versions.

I was perusing Yahoo! Answers looking for interesting questions and I ran across one that asked how you can add an line that represents the average of a data series to a chart of that series. The most obvious way to do this would be to use a helper column with the Average() function in each cell. It would be the same value all the way down the column, and then you could just add that series to the line chart. Of course, the problem is that you have to remember to copy the Average() function down every time you add a new data item. There has to be a better way.

Of course there is, and this post will show my solution. It involves using defined names (AKA range names). Defined names are a powerful tool, not because they allow you to give a name to a static range (or cell) on your worksheet, but because defined names are really formulas. In Excel 2003, go to Insert --> Name --> Define to access the Define Name dialog box (in Excel 2007 Define Name is located on the Formulas ribbon). Notice the box at the bottom that is labeled “Refers to:” In that box, the default entry is something like =Sheet1!$A$1 (it will be the address of the active cell or range). It is the equals sign that is the giveaway. These defined names are actually formulas.

The first thing that we want to do is to create a defined name that references our original data, and that will also expand as we add new data. In other words, we want to create a dynamic range name. To do this, we need to create a reference using the Offset() function and the Count() function. The goal of these two functions will be to find the last data point in the column. The Count() function will tell us how many numbers are in the column, and the Offset function will create a reference to that last data point. In my sample worksheet, I have “Random Data” as the label in A1, and then 23 numbers below that. So, my data is going to be in column A. Now, go to Insert --> Name --> Define and then enter the name ChartData at the top. Now, click in the “Refers to:” box at the bottom and enter the following formula:

=Sheet1!$A$2:Offset($A$2,Count(Sheet1!$A:$A)-1,0)

That will create a dynamic reference from A2 to the last data point. Make sure that you use absolute references (the dollar signs) in the formula. Note that I used Count(A:A) to get a count of the numbers in column A and then subtract 1. I am subtracting 1 because otherwise the Offset function would reference one row after the data ends.

Now, create a line chart of the original data (I just generated some random numbers for my sample worksheet. Click on the line for the data series and then look in the formula bar. You should see the Series() function that Excel uses to define the chart. The formula will look something like this:

=SERIES(Sheet1!$A$1,,Sheet1!$A$2:$A$24,1)

The first reference is the name of the data series, and the second is the range that holds the data. We want to replace the data range with our dynamic range name so that the formula looks like this:

=SERIES(Sheet1!$A$1,,Sheet1!ChartData,1)

You can do that directly in the formula bar by editing the Series() function. We now have a dynamic chart. If you add new data to the series, the ChartData dynamic range will automatically expand and the chart will expand to include the new data as well.

We are now halfway done. At this point, we want to add another series to the chart that represents the average of all of the data points. It will be a straight line all of the way across the chart, and it will move up and down as the average changes because of the new data that will be added.

Again, we will use a defined name. However, in this case we need to create an array function because we need to show the average at each data point. In other words, the new series will have exactly as many data points as the original data series, but each point will be the average. To do this, I need to create an array such as {0.49;0.49;0.49;0.49}. So, I need the average to repeat the same number of times as there are data points. There are probably other ways to do this, but here is what I used:

=IsNumber(ChartData)*Average(ChartData)

Create a defined name called Avg and set it to that formula. Note that the IsNumber() function evaluates each data point in the original series to see if it is a number or not. If so, then it returns TRUE. I know that every data point is a number, so I’m using this to generate an array of TRUE with the same number of values as the original data. Now, when I multiply that array of Trues by the average, Excel will convert TRUE to its numeric representation (which is a 1), and create an array that contains the average repeated N times (such as the example above).

At this point, all that remains is to add the Avg series to the chart. To do that, just right click in the chart and choose Source Data. Go to the Series tab and click the Add button. In the Values box, type:

=Sheet1!Avg

and the click the OK button. You should now have a horizontal line representing the average in your chart. The picture below shows an example:

An example workbook is available.

Comments:

Name:

Email (will not be displayed):

Location:

URL:

Smileys

Remember my personal information

Notify me of follow-up comments?

Submit the word you see below:

<< Back to main