|
|
|
Charting Dynamic Normalized Ranges.Often it is desirable to plot multiple series so that their values are normalized to an initial value of, say, 100. This is particularly useful when comparing performance of stock prices on a relative basis. This article shows how to use dynamic ranges and dynamic charts to display normalized values. More examples of dynamic charts can be found by following links on Dynamic and Interactive Charts. The Data
Defining The NamesA "Name" is what Excel calls a variable. A Name has two parts, a name and a formula that defines what the Name refers to. Sometimes a Name is called a "Named Range", but a more accurate description would be "Named Formula". If I wanted a Name to refer to a constant, I would give it a name like Two and a refers to formula of =2. If I wanted another name to refer to a range, I might name it MyRange and assign it a formula of =Sheet1!$A$1:$C$10. Names can be more complicated than this, incorporating Excel's worksheet formulas to produce almost any result. For example, the names defined below include both OFFSET and INDEX functions. It helps to give consideration to the names of your Names. The name of a Name can use letters and numerals and a few punctuation marks, but it's safer to limit these to the underscore character and the period (and I don't even use periods); the name must start with a letter or an underscore. Since the Names in this example all refer to worksheet ranges, I use a prefix of "rng". Often, to indicate ranges used for chart data, I use a prefix of "cht" for the prefix. Use a convention that makes sense and will help you remember two months later what the Names refer to. For example, in this example, I use "rngValue1" to refer to the entire set of values for series 1, "rngSelectedValue1" to refer to the selected subset of the series 1 values, and "rngSelectedValue1Norm" to refer to the normalized selected values of series 1. The table below describes the Names used in this example:
Note for Excel 2007 users: This technique will fail if your Names begin with the word "Chart". Use names like ChtValues instead of ChartValues. To define a Name which is based on a static range, select the range, and type a name in the Name Box (the small box above the top left cell, to the left of the formula bar), and press Enter. To define any Name, go to the Insert menu, select Name, then select Define (or use the shortcut Ctrl+F3). The Define Name dialog has a Name box for entering a name for a Name, a Names in Workbook listbox showing names that have already been defined, and a Refers To box for the refers-to formula. Simply enter a name and a formula, and click Add. The scope of a Name, that is, the amount of the environment that it is relevant within, can be changed by how the Name is named. To define a Name that references a worksheet only, enter its name with the worksheet name as a prefix: DataSheet!rngDates. This limits the scope of the Name to the worksheet. If the name does not include a worksheet name, the scope of the Name is the whole workbook. The Names in this example have been defined for the entire workbook. The OFFSET formula consists of five parameters: a reference range, a row offset, a column offset, an optional row count, and an optional column count. For example, =OFFSET(DataSheet!$A$1,1,0,COUNT(DataSheet!$A:$A),1) means the defined Name starts at DataSheet!$A$1, but offset down 1 row and right 0 columns, and is COUNT(DataSheet!$A:$A) rows high (the number of numerical values counted in column A, and remember that a date is a numerical value) and 1 columns wide. =OFFSET(rngDates,,1) means the defined Name consists of the range defined by rngDates, but offset down 0 rows and right 1 columns. The absence of row and column count parameters means the new Name is the same size as the reference range rngDates. =rngSelectedValue1/INDEX(rngSelectedValue1,1)*100 means the new Name consists of the values in the Name rngSelectedValue1, divided by INDEX(rngSelectedValue1,1), that is, the first value in rngSelectedValue1), and multiplied by 100. I find it helpful to display the values of my Names in a worksheet to make sure I've properly defined them. The table below shows how values for all of my date and value Names. To populate a worksheet range with a Name, you select the worksheet range, then array-enter a formula referring to the Name. This means you type a formula like =rngDates, then hold Ctrl and Shift while pressing Enter. If done correctly, Excel places curly braces around the formula: {=rngDates}. I use a larger range than my Names require, for example, H2:H35 for rngDates; any excess cells beyond the end of a Name are filled with #N/A errors.
Creating The Dynamic Charts1. A static chart containing all of the dataThe chart is initially created by selecting the range A1:C32 and creating a line chart.
The series formulas for the two plotted series are shown below. Note that they include fixed links to static worksheet ranges. =SERIES(DataSheet!$B$1,DataSheet!$A$2:$A$32,DataSheet!$B$2:$B$32,1)
=SERIES(DataSheet!$C$1,DataSheet!$A$2:$A$32,DataSheet!$C$2:$C$32,2)
2. A dynamic chart containing all of the dataTo convert each chart series to a dynamic series, change the references in the formula from fixed worksheet ranges (i.e., $A$2:$A$32 or $B$2:$B$32) to defined Names (rngDates or rngValue1). For me, the easiest way to do this is to select the series, and edit the series formula directly in the formula bar. You can also select Source Data from the Chart menu, click on the Series tab, select a series, and edit the references in the input boxes in this dialog. =SERIES(DataSheet!$B$1,DataSheet!rngDates,DataSheet!rngValue1,1)
=SERIES(DataSheet!$C$1,DataSheet!rngDates,DataSheet!rngValue2,2)
Since the Names were defined for the entire workbook, Excel changes the worksheet name in the range reference (DataSheet) to the workbook name (Normalized.xls): =SERIES(DataSheet!$B$1,Normalized.xls!rngDates,Normalized.xls!rngValue1,1)
=SERIES(DataSheet!$C$1,Normalized.xls!rngDates,Normalized.xls!rngValue2,2)
This chart appears the same as the static chart above, but it is dynamic. If rows were added or removed from the data range, the chart would automatically update to show all data in the range.
3. A dynamic chart containing a selection of the dataTo change the dynamic chart so it includes only the selected data defined by the rngStart and rngLength parameters, change the Names in the series formulas (rngDates, rngValue1) to the Names that reference the selected ranges (rngSelectedDates, rngSelectedValue1) =SERIES(DataSheet!$B$1,Normalized.xls!rngSelectedDates,Normalized.xls!rngSelectedValue1,1)
=SERIES(DataSheet!$C$1,Normalized.xls!rngSelectedDates,Normalized.xls!rngSelectedValue2,2)
4. A dynamic chart containing a selection of the data, normalized to 100 at the starting pointFinally, to show the normalized values in the chart, change from the selected Names (rngSelectedValue1) to the normalized Names (rngSelectedValue1Norm) in the series formula: =SERIES(DataSheet!$B$1,Normalized.xls!rngSelectedDates,Normalized.xls!rngSelectedValue1Norm,1)
=SERIES(DataSheet!$C$1,Normalized.xls!rngSelectedDates,Normalized.xls!rngSelectedValue2Norm,2)
The chart above shows the data for the original starting value of 5 and length value of 12. When these values are changed to 13 and 19, the chart automatically adjusts.
Here are more Dynamic Chart resources on this web site:
For a partial list of internet links to dynamic charting, click here. Alse see Dynamic Charts on the PTS Blog. |
Peltier Technical Services, Inc.Excel Chart Add-Ins | Training | Charts and Tutorials | PTS BlogPeltier Technical Services, Inc., Copyright © 2017. All rights reserved. |
