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" wrote in message
...
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" wrote in message
...
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/
_______
"Michel Gerday" wrote in message
...
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 Gerday
Belgium