Dynamic Step Chart using range names

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Let's create a step chart to illustrate Fed Funds Rates

Data at http://www.federalreserve.gov/fomc/fundsrate.htm

I create a new book and save it as "Step.xls" (a short name is good idea...
see at III)

| A B
--|----------- ----
1| Date Fed
2|03-Jan-2000 5.50
3|02-Feb-2000 5.75
4|21-Mar-2000 6.00
5|16-May-2000 6.50
6|03-Jan-2001 6.00
7|31-Jan-2001 5.50
8|15-Feb-2001 5.50
9|

II. I have to define Range Names with Insert,Name,Define...

1. Date: =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
=> I start from cell A2 because A1 contain the label
=> I subtract 1 because label in cell A1 was counted

2. DateX: =OFFSET(Sheet1!$A$2,1,0,COUNTA(Sheet1!$A:$A)-2,1)
=> I want the same range of dates except the first date

3. Fed: =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)
=> Same as for Date

4. FedX: =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-2,1)
=> I want the same range of Fed values except the last one

5. FedLabel: =Sheet1!$B$1 (not mandatory)

III. I have to create the chart

Insert,Chart...,Line,Line chart without marker,Next >
Select the "Series" tab
Click on "Add" series
- Name: =Step!FedLabel (Spreadsheet name is mandatory!)
- Values: =Step!FedX,Step!Fed (Union of 2 ranges !)
- (X) axis: =Step!DateX,Step!Date

Click on Finish... That's it !

IV. When the Fed changes its rates, feel free to add a row of data to see
the chart change dynamically...
* * *
Notes:
1. This works because Excel recognised the Date ranges as a Time Scale X
axis (as mentioned early by Andy Pope at http://www.andypope.info).
2. It also works with an X axis with x > 0
3. Done with Excel 2002.
 
Michel -

Very nice one. The only defect I found was in the definition of the Y
values. You have to list the areas of the range in the opposite order:
=Step!Fed,Step!FedX instead of =Step!FedX,Step!Fed.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Sorry, Michel. Your post was perfectly correct. I made an error when I
defined the FedX name, so my procedure was flawed.

- Jon


Jon Peltier said:
Michel -

Very nice one. The only defect I found was in the definition of the Y
values. You have to list the areas of the range in the opposite order:
=Step!Fed,Step!FedX instead of =Step!FedX,Step!Fed.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
Click to expand...
 
Dynamic Step Chart using range names
(Rev 1)

Let's create a step chart to illustrate Fed Funds Rates
Data at http://www.federalreserve.gov/fomc/fundsrate.htm

I create a new book and save it as "Step.xls" (a short name is good idea...
see at III)

| A B
--|----------- ----
1| Date Fed
2|03-Jan-2000 5.50
3|02-Feb-2000 5.75
4|21-Mar-2000 6.00
5|16-May-2000 6.50
6|03-Jan-2001 6.00
7|31-Jan-2001 5.50
8|15-Feb-2001 5.50
9|

II. I have to define Range Names with Insert,Name,Define...

1. NbDate =COUNT(Sheet1!$A:$A)
=> I count the number of cells in that contain numbers

2. Date: =OFFSET(Sheet1!$A$2,0,0,NbDate,1)
=> I start from cell A2 because A1 contain the label
=> I subtract 1 because label in cell A1 was counted

3. DateX: =OFFSET(Sheet1!$A$2,1,0,NbDate - 1,1)
=> I want the same range of dates except the first date

4. Fed: =OFFSET(Sheet1!$B$2,0,0,NbDate,1)
=> Same as for Date

5. FedX: =OFFSET(Sheet1!$B$2,0,0,NbDate - 1,1)
=> I want the same range of Fed values except the last one

6. FedLabel: =Sheet1!$B$1 (not mandatory)

III. I have to create the chart

Insert,Chart...,Line,Line chart without marker,Next >
Select the "Series" tab
Click on "Add" series
- Name: =Step.xls!FedLabel (Spreadsheet name is mandatory!)
- Values: =(Step.xls!FedX,Step.xls!Fed) (Union of 2 ranges !)
- (X) axis: =(Step.xls!DateX,Step.xls!Date)
=> the comma is the character defined as "List separator"
in the Windows Regional and Language Options.

Click on Finish... That's it !

IV. When the Fed changes its rates, feel free to add a row of
data to see the chart change dynamically...

===
Notes:
1. This works because Excel recognised the Date ranges as a Time Scale X
axis (as mentioned early by Andy Pope at http://www.andypope.info).
2. It also works with a "number" X axis with x > 0
3. Done with Excel 2002.
4. Rev 1: use of NbDate, COUNT instead of COUNTA, full spreadsheet name.

Post Scriptum: Thanks Jon for your comments.
 
That's a nice way to use named formulas to create a step chart. :)

A few comments.

Don't use Date as a name since it is a native XL function.

II.2 has an erroneous comment about subtracting 1.

Define subsequent names using already existing names. That makes
maintenance easier and IMO the design more transparent. For example (with
XL in R1C1 mode):

AllDates =OFFSET(Sheet1!R2C1,0,0,COUNTA(Sheet1!C1)-1,1)
AllDatesX =OFFSET(AllDates,1,0,COUNTA(AllDates)-1,1)
FedRate =OFFSET(AllDates,0,1)
FedRateX =OFFSET(FedRate,0,0,COUNT(FedRate)-1,1)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Anyone still watching this thread can see Michel's example in comparison
with the "usual" error bar method on this web page:

http://peltiertech.com/Excel/ChartsHowTo/StepChart.html

Thanks to Michel for graciously allowing me to present his technique.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Jon Peltier said:
Sorry, Michel. Your post was perfectly correct. I made an error when I
defined the FedX name, so my procedure was flawed.

- Jon
Click to expand...
 
Back
Top