Dynamic expanding chart

J

Josh M

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?
 
J

Jon Peltier

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
 
J

Josh M

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(A3>0,(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
 
J

Josh M

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(A3>0,(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.
 
J

Jon Peltier

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(AllClose),1)))/INDEX(AllClose,ROW(OFFSET(Sheet1!$A$1,0,0,ROWS(AllClose),1))-1)-1

Use AllChange as the Y values of the series.

- Jon
 
J

Josh M

That is great. Thank you!

Jon Peltier said:
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(AllClose),1)))/INDEX(AllClose,ROW(OFFSET(Sheet1!$A$1,0,0,ROWS(AllClose),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
_______
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top