|
Dynamic Step Chart using range names
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 |
Dynamic Step Chart using range names
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 |
Dynamic Step Chart using range names
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 |
Revised version 1
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. |
Revised version 1
Thanks for your advices.
-- Michel Gerday Belgium "Tushar Mehta" a écrit : 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 |
Dynamic Step Chart using range names
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 |
| All times are GMT +1. The time now is 04:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com