Line Charts - Is this possible?

[Monomeeth]

I have developed a budget tracking tool in Excel and one of the reports I
need to generate is a line chart which displays a Cumulative Forecast Amount
by month and a Cumulative Actual Spend by month.

I have used a table with formulas to calculate the figures for each month.
Below is an example:

JUL AUG SEP OCT NOV DEC
CFA $1830 $3490 $5690 $6100 $8000 $8600
CAS $1570 $2274 $2274 $2274 $2274 $2274

As you can see, the Cumulative Actual Spend figures for Sept onwards are
identical as we haven't completed those months.

Currently, my Line Chart shows a line for CFA for each month of the year,
and another line for CAS also for each month of the year. Of course, in the
above example, the CAS line flatlines for the entire period of Aug to Dec.

What I want is a way for the line chart only to display the CAS line up to
and including the current month. In other words, this line will be shorter
for 11 months of the year, only growing at the end of each month.

Is there some way to automate this via chart settings, formulas, and/or
programming?

APOLOGY
I have posted this question to the Excel Charts, Excel Programming and Excel
Worksheet Functions newsgroups as I did not know the full USENET designation
to "cross-post" this properly. Can someone advise me for future reference?

Thanks in advance.

Your help would be most appreciated.

Joe.

 

[Monomeeth]

Thanks greatly for your help, on both counts.

I will take a look at those resources and see if I can make sense of it all.

Joe.
--
If you can measure it, you can improve it!

I have developed a budget tracking tool in Excel and one of the reports I
need to generate is a line chart which displays a Cumulative Forecast Amount
by month and a Cumulative Actual Spend by month.

I have used a table with formulas to calculate the figures for each month.
Below is an example:

JUL AUG SEP OCT NOV DEC
CFA $1830 $3490 $5690 $6100 $8000 $8600
CAS $1570 $2274 $2274 $2274 $2274 $2274

As you can see, the Cumulative Actual Spend figures for Sept onwards are
identical as we haven't completed those months.

Currently, my Line Chart shows a line for CFA for each month of the year,
and another line for CAS also for each month of the year. Of course, in the
above example, the CAS line flatlines for the entire period of Aug to Dec.

What I want is a way for the line chart only to display the CAS line up to
and including the current month. In other words, this line will be shorter
for 11 months of the year, only growing at the end of each month.

Is there some way to automate this via chart settings, formulas, and/or
programming?

APOLOGY
I have posted this question to the Excel Charts, Excel Programming and Excel
Worksheet Functions newsgroups as I did not know the full USENET designation
to "cross-post" this properly. Can someone advise me for future reference?

Thanks in advance.

Your help would be most appreciated.

Hi Joe,

I think leveraging dynamic [named] ranges would serve you well here.
Chip Pearson has a good tutorial on the subject here:
http://www.cpearson.com/excel/named.htm

Another good one here:
http://www.ozgrid.com/Excel/DynamicRanges.htm

The examples one often sees handle vertically changing ranges, and
seeking the last non-blank/non-numeric entry, but if you can master the
heart of the technique--the OFFSET function--you can adapt it to do what
you are asking.

Re: your Apology,

Unfortunately responses to your question will be fragmented, and you
might get some flack for multiposting. The correct way to cross-post
varies from platform to platform. It looks like you are using a web
interface (?). In general, to cross-post you string the newsgroups
together separated by semicolons (, but I can't say for sure this is
how you would do it. Also, cross-posting to more than three groups (some
would say two) is considered spam. Hope this helps!

 

[access dummie]

I have a similar issue where we are adding a month's data in a column to the
right. I chart information for the last 24 months. I am looking for a way
to automate "rolling" the 24 month chart. I don't quite follow how to use
these functions to achieve this.

I have developed a budget tracking tool in Excel and one of the reports I
need to generate is a line chart which displays a Cumulative Forecast Amount
by month and a Cumulative Actual Spend by month.

I have used a table with formulas to calculate the figures for each month.
Below is an example:

JUL AUG SEP OCT NOV DEC
CFA $1830 $3490 $5690 $6100 $8000 $8600
CAS $1570 $2274 $2274 $2274 $2274 $2274

As you can see, the Cumulative Actual Spend figures for Sept onwards are
identical as we haven't completed those months.

Currently, my Line Chart shows a line for CFA for each month of the year,
and another line for CAS also for each month of the year. Of course, in the
above example, the CAS line flatlines for the entire period of Aug to Dec.

What I want is a way for the line chart only to display the CAS line up to
and including the current month. In other words, this line will be shorter
for 11 months of the year, only growing at the end of each month.

Is there some way to automate this via chart settings, formulas, and/or
programming?

APOLOGY
I have posted this question to the Excel Charts, Excel Programming and Excel
Worksheet Functions newsgroups as I did not know the full USENET designation
to "cross-post" this properly. Can someone advise me for future reference?

Thanks in advance.

Your help would be most appreciated.

Hi Joe,

I think leveraging dynamic [named] ranges would serve you well here.
Chip Pearson has a good tutorial on the subject here:
http://www.cpearson.com/excel/named.htm

Another good one here:
http://www.ozgrid.com/Excel/DynamicRanges.htm

The examples one often sees handle vertically changing ranges, and
seeking the last non-blank/non-numeric entry, but if you can master the
heart of the technique--the OFFSET function--you can adapt it to do what
you are asking.

Re: your Apology,

Unfortunately responses to your question will be fragmented, and you
might get some flack for multiposting. The correct way to cross-post
varies from platform to platform. It looks like you are using a web
interface (?). In general, to cross-post you string the newsgroups
together separated by semicolons (, but I can't say for sure this is
how you would do it. Also, cross-posting to more than three groups (some
would say two) is considered spam. Hope this helps!

 

[Jon Peltier]

You can probably adapt this last-12-months example to your data:

http://peltiertech.com/Excel/Charts/DynamicLast12.html

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

I have a similar issue where we are adding a month's data in a column to
the
right. I chart information for the last 24 months. I am looking for a
way
to automate "rolling" the 24 month chart. I don't quite follow how to use
these functions to achieve this.

I have developed a budget tracking tool in Excel and one of the reports
I
need to generate is a line chart which displays a Cumulative Forecast
Amount
by month and a Cumulative Actual Spend by month.

I have used a table with formulas to calculate the figures for each
month.
Below is an example:

JUL AUG SEP OCT NOV DEC
CFA $1830 $3490 $5690 $6100 $8000 $8600
CAS $1570 $2274 $2274 $2274 $2274 $2274

As you can see, the Cumulative Actual Spend figures for Sept onwards
are
identical as we haven't completed those months.

Currently, my Line Chart shows a line for CFA for each month of the
year,
and another line for CAS also for each month of the year. Of course, in
the
above example, the CAS line flatlines for the entire period of Aug to
Dec.

What I want is a way for the line chart only to display the CAS line up
to
and including the current month. In other words, this line will be
shorter
for 11 months of the year, only growing at the end of each month.

Is there some way to automate this via chart settings, formulas, and/or
programming?

APOLOGY
I have posted this question to the Excel Charts, Excel Programming and
Excel
Worksheet Functions newsgroups as I did not know the full USENET
designation
to "cross-post" this properly. Can someone advise me for future
reference?

Thanks in advance.

Your help would be most appreciated.

Hi Joe,

I think leveraging dynamic [named] ranges would serve you well here.
Chip Pearson has a good tutorial on the subject here:
http://www.cpearson.com/excel/named.htm

Another good one here:
http://www.ozgrid.com/Excel/DynamicRanges.htm

The examples one often sees handle vertically changing ranges, and
seeking the last non-blank/non-numeric entry, but if you can master the
heart of the technique--the OFFSET function--you can adapt it to do what
you are asking.

Re: your Apology,

Unfortunately responses to your question will be fragmented, and you
might get some flack for multiposting. The correct way to cross-post
varies from platform to platform. It looks like you are using a web
interface (?). In general, to cross-post you string the newsgroups
together separated by semicolons (, but I can't say for sure this is
how you would do it. Also, cross-posting to more than three groups (some
would say two) is considered spam. Hope this helps!