Dynamic expanding chart
 

I have used dynamic ranges to create a chart that expands as new data is
added. I get economic data from an external provider which I update/download
daily or weekly. New data is added in new rows I refresh from the data
source and the chart expands automatically. The data is simply the Date in
column A and the value in column B.

However, I would like to chart the results of a formula in a column next to
the data from the source instead. For example, I would like column C to be
the percentage change from the prior period, which is what I want to chart.

Is there a way to extend the formula to the next row automatically as data
is added?

Dynamic expanding chart
 

In your name definitions, there is probably an OFFSET function. You need
only adjust the column offset (the third argument of OFFSET) so the
definition points to the desired column. In fact, you need to increase it by
1 to move one column to the right.

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


"Josh M" wrote in message
...
I have used dynamic ranges to create a chart that expands as new data is
added. I get economic data from an external provider which I
update/download
daily or weekly. New data is added in new rows I refresh from the data
source and the chart expands automatically. The data is simply the Date
in
column A and the value in column B.

However, I would like to chart the results of a formula in a column next
to
the data from the source instead. For example, I would like column C to
be
the percentage change from the prior period, which is what I want to
chart.

Is there a way to extend the formula to the next row automatically as data
is added?

Dynamic expanding chart
 

Thank you Jon, adjusting the column number in the OFFSET formula enabled me
to chart the correct column. However, is there another (easier) way to have
the formula in column C expand automatically as new data rows are entered
into Columns A & B, other than copying the formula all the way down the sheet?

Data in columns A & B are updated automatically from and outside source.
Column C is a formula I put in to find the percentage change from the
previous day. (=B3/B2-1)

A B C
Date S&P Close % Change

1/1/07 100
1/2/07 150 50%
1/3/07 125 -16.67%
1/4/07 140 12%

I used a dynamic range "AllDates", for column A that expands automatically
as new data is added by using the following formula, & that works great for
my chart to expand:

='S&P'!$A$5:INDEX('S&P'!$A:$A,MATCH(9.99999E+307,' S&P'!$A:$A))

But for column C, I wanted the percentage change from the previous period
and just copied the following formula well below the last line of data:

=IF(A30,(B3/B2-1),NA())

Is there a way, other than dragging that formula down, to have the formula
in column C expand as new data rows are added in A & B? Do I use a similar
formula as I did for the dates?

Thank you very much!
Josh

"Jon Peltier" wrote:

In your name definitions, there is probably an OFFSET function. You need
only adjust the column offset (the third argument of OFFSET) so the
definition points to the desired column. In fact, you need to increase it by
1 to move one column to the right.

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


"Josh M" wrote in message
...
I have used dynamic ranges to create a chart that expands as new data is
added. I get economic data from an external provider which I
update/download
daily or weekly. New data is added in new rows I refresh from the data
source and the chart expands automatically. The data is simply the Date
in
column A and the value in column B.

However, I would like to chart the results of a formula in a column next
to
the data from the source instead. For example, I would like column C to
be
the percentage change from the prior period, which is what I want to
chart.

Is there a way to extend the formula to the next row automatically as data
is added?

Dynamic expanding chart
 

Thank you Jon, adjusting the column number in the OFFSET formula enabled me
to chart the correct column. However, is there another (easier) way to have
the formula in column C expand automatically as new data rows are entered
into Columns A & B, other than copying the formula all the way down the sheet?

Data in columns A & B are updated automatically from and outside source.
Column C is a formula I put in to find the percentage change from the
previous day. (=B3/B2-1)

A B C
Date S&P Close % Change

1/1/07 100
1/2/07 150 50%
1/3/07 125 -16.67%
1/4/07 140 12%

I used a dynamic range "AllDates", for column A that expands automatically
as new data is added by using the following formula, & that works great for
my chart to expand:

='S&P'!$A$5:INDEX('S&P'!$A:$A,MATCH(9.99999E+307,' S&P'!$A:$A))

But for column C, I wanted the percentage change from the previous period
and just copied the following formula well below the last line of data:

=IF(A30,(B3/B2-1),NA())

Is there a way, other than dragging that formula down, to have the formula
in column C expand as new data rows are added in A & B? Do I use a similar
formula as I did for the dates?

Thank you, Josh.

"Jon Peltier" wrote:

In your name definitions, there is probably an OFFSET function. You need
only adjust the column offset (the third argument of OFFSET) so the
definition points to the desired column. In fact, you need to increase it by
1 to move one column to the right.

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


"Josh M" wrote in message
...
I have used dynamic ranges to create a chart that expands as new data is
added. I get economic data from an external provider which I
update/download
daily or weekly. New data is added in new rows I refresh from the data
source and the chart expands automatically. The data is simply the Date
in
column A and the value in column B.

However, I would like to chart the results of a formula in a column next
to
the data from the source instead. For example, I would like column C to
be
the percentage change from the prior period, which is what I want to
chart.

Is there a way to extend the formula to the next row automatically as data
is added?

Dynamic expanding chart
 

Define AllDates as you have.
Define AllClose as:

=OFFSET(AllDates,0,1)

Define AllChange as:
=INDEX(AllClose,ROW(OFFSET(Sheet1!$A$1,0,0,ROWS(Al lClose),1)))/INDEX(AllClose,ROW(OFFSET(Sheet1!$A$1,0,0,ROWS(All Close),1))-1)-1

Use AllChange as the Y values of the series.

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


"Josh M" wrote in message
...
Thank you Jon, adjusting the column number in the OFFSET formula enabled
me
to chart the correct column. However, is there another (easier) way to
have
the formula in column C expand automatically as new data rows are entered
into Columns A & B, other than copying the formula all the way down the
sheet?

Data in columns A & B are updated automatically from and outside source.
Column C is a formula I put in to find the percentage change from the
previous day. (=B3/B2-1)

A B C
Date S&P Close % Change

1/1/07 100
1/2/07 150 50%
1/3/07 125 -16.67%
1/4/07 140 12%

I used a dynamic range "AllDates", for column A that expands
automatically
as new data is added by using the following formula, & that works great
for
my chart to expand:

='S&P'!$A$5:INDEX('S&P'!$A:$A,MATCH(9.99999E+307,' S&P'!$A:$A))

But for column C, I wanted the percentage change from the previous period
and just copied the following formula well below the last line of data:

=IF(A30,(B3/B2-1),NA())

Is there a way, other than dragging that formula down, to have the formula
in column C expand as new data rows are added in A & B? Do I use a
similar
formula as I did for the dates?

Thank you, Josh.

"Jon Peltier" wrote:

In your name definitions, there is probably an OFFSET function. You need
only adjust the column offset (the third argument of OFFSET) so the
definition points to the desired column. In fact, you need to increase it
by
1 to move one column to the right.

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


"Josh M" wrote in message
...
I have used dynamic ranges to create a chart that expands as new data is
added. I get economic data from an external provider which I
update/download
daily or weekly. New data is added in new rows I refresh from the
data
source and the chart expands automatically. The data is simply the
Date
in
column A and the value in column B.

However, I would like to chart the results of a formula in a column
next
to
the data from the source instead. For example, I would like column C
to
be
the percentage change from the prior period, which is what I want to
chart.

Is there a way to extend the formula to the next row automatically as
data
is added?

Dynamic expanding chart
 

That is great. Thank you!

"Jon Peltier" wrote:

Define AllDates as you have.
Define AllClose as:

=OFFSET(AllDates,0,1)

Define AllChange as:
=INDEX(AllClose,ROW(OFFSET(Sheet1!$A$1,0,0,ROWS(Al lClose),1)))/INDEX(AllClose,ROW(OFFSET(Sheet1!$A$1,0,0,ROWS(All Close),1))-1)-1

Use AllChange as the Y values of the series.

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


"Josh M" wrote in message
...
Thank you Jon, adjusting the column number in the OFFSET formula enabled
me
to chart the correct column. However, is there another (easier) way to
have
the formula in column C expand automatically as new data rows are entered
into Columns A & B, other than copying the formula all the way down the
sheet?

Data in columns A & B are updated automatically from and outside source.
Column C is a formula I put in to find the percentage change from the
previous day. (=B3/B2-1)

A B C
Date S&P Close % Change

1/1/07 100
1/2/07 150 50%
1/3/07 125 -16.67%
1/4/07 140 12%

I used a dynamic range "AllDates", for column A that expands
automatically
as new data is added by using the following formula, & that works great
for
my chart to expand:

='S&P'!$A$5:INDEX('S&P'!$A:$A,MATCH(9.99999E+307,' S&P'!$A:$A))

But for column C, I wanted the percentage change from the previous period
and just copied the following formula well below the last line of data:

=IF(A30,(B3/B2-1),NA())

Is there a way, other than dragging that formula down, to have the formula
in column C expand as new data rows are added in A & B? Do I use a
similar
formula as I did for the dates?

Thank you, Josh.

"Jon Peltier" wrote:

In your name definitions, there is probably an OFFSET function. You need
only adjust the column offset (the third argument of OFFSET) so the
definition points to the desired column. In fact, you need to increase it
by
1 to move one column to the right.

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


"Josh M" wrote in message
...
I have used dynamic ranges to create a chart that expands as new data is
added. I get economic data from an external provider which I
update/download
daily or weekly. New data is added in new rows I refresh from the
data
source and the chart expands automatically. The data is simply the
Date
in
column A and the value in column B.

However, I would like to chart the results of a formula in a column
next
to
the data from the source instead. For example, I would like column C
to
be
the percentage change from the prior period, which is what I want to
chart.

Is there a way to extend the formula to the next row automatically as
data
is added?