+ Reply to Thread
Results 1 to 7 of 7

Dynamic Step Chart using range names

  1. #1
    Michel Gerday
    Guest

    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

  2. #2
    Jon Peltier
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  3. #3
    Jon Peltier
    Guest

    Re: 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" <[email protected]> wrote in message
    news:%[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    >> 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

    >
    >




  4. #4
    Michel Gerday
    Guest

    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.

  5. #5
    Tushar Mehta
    Guest

    Re: Revised version 1

    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

    In article <[email protected]>,
    [email protected] says...
    > 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.
    >


  6. #6
    Michel Gerday
    Guest

    Re: 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
    >



  7. #7
    Jon Peltier
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:%[email protected]...
    >> 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" <[email protected]> wrote in message
    >> news:[email protected]...
    >>> 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

    >>
    >>

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1