[SOLVED] Dynamic Charting - i think!

Hi,

I have a spreadsheet that works out loan interest and cumulative
interest.

I enter the loan amount, APR and period in Cells A1, A2 & A3
respectively.

Then, in a table below (using IF statements) the data is filled out.

i.e. if I put in a period of 36months, the period column fills out
until it counts to 36.
(If I put 60 months, it counts until it reaches 60 etc.etc.)

I would like to construct a chart that displays the prinicipal of the
loan that is paid, the cumulative interest that is paid, against a
'running' x-axis based on the period.

Here's the tricky bit... I want the chart to auto update when the
period is changed.

i.e. if period = 36, then the chart x-axis goes up to 36. If I then
change the period to 60, then the same chart alters its x-axis and plot
to show figures upto 60...

Can this be done...

I'm in a similar situation. I simply made the chart to show the longest
period possible. Most people don't complain about half the chart being
empty. I just filled the rest of the chart if if statements. Modified for
you, just use =IF(Duration<={Corresponding Period},"",{calculation}. The if
on the data keeps a ton of 0s from showing on the graph. This isn't perfect,
but it works for me.

"[email protected]" wrote:

> Hi,
>
> I have a spreadsheet that works out loan interest and cumulative
> interest.
>
> I enter the loan amount, APR and period in Cells A1, A2 & A3
> respectively.
>
> Then, in a table below (using IF statements) the data is filled out.
>
> i.e. if I put in a period of 36months, the period column fills out
> until it counts to 36.
> (If I put 60 months, it counts until it reaches 60 etc.etc.)
>
> I would like to construct a chart that displays the prinicipal of the
> loan that is paid, the cumulative interest that is paid, against a
> 'running' x-axis based on the period.
>
> Here's the tricky bit... I want the chart to auto update when the
> period is changed.
>
> i.e. if period = 36, then the chart x-axis goes up to 36. If I then
> change the period to 60, then the same chart alters its x-axis and plot
> to show figures upto 60...
>
> Can this be done...
>
>

I'm in a similar situation. I simply made the chart to show the longest
period possible. Most people don't complain about half the chart being
empty. I just filled the rest of the chart if if statements. Modified for
you, just use =IF(Duration<={Corresponding Period},"",{calculation}. The if
on the data keeps a ton of 0s from showing on the graph. This isn't perfect,
but it works for me.

"[email protected]" wrote:

> Hi,
>
> I have a spreadsheet that works out loan interest and cumulative
> interest.
>
> I enter the loan amount, APR and period in Cells A1, A2 & A3
> respectively.
>
> Then, in a table below (using IF statements) the data is filled out.
>
> i.e. if I put in a period of 36months, the period column fills out
> until it counts to 36.
> (If I put 60 months, it counts until it reaches 60 etc.etc.)
>
> I would like to construct a chart that displays the prinicipal of the
> loan that is paid, the cumulative interest that is paid, against a
> 'running' x-axis based on the period.
>
> Here's the tricky bit... I want the chart to auto update when the
> period is changed.
>
> i.e. if period = 36, then the chart x-axis goes up to 36. If I then
> change the period to 60, then the same chart alters its x-axis and plot
> to show figures upto 60...
>
> Can this be done...
>
>

I'm in a similar situation. I simply made the chart to show the longest
period possible. Most people don't complain about half the chart being
empty. I just filled the rest of the chart if if statements. Modified for
you, just use =IF(Duration<={Corresponding Period},"",{calculation}. The if
on the data keeps a ton of 0s from showing on the graph. This isn't perfect,
but it works for me.

"[email protected]" wrote:

> Hi,
>
> I have a spreadsheet that works out loan interest and cumulative
> interest.
>
> I enter the loan amount, APR and period in Cells A1, A2 & A3
> respectively.
>
> Then, in a table below (using IF statements) the data is filled out.
>
> i.e. if I put in a period of 36months, the period column fills out
> until it counts to 36.
> (If I put 60 months, it counts until it reaches 60 etc.etc.)
>
> I would like to construct a chart that displays the prinicipal of the
> loan that is paid, the cumulative interest that is paid, against a
> 'running' x-axis based on the period.
>
> Here's the tricky bit... I want the chart to auto update when the
> period is changed.
>
> i.e. if period = 36, then the chart x-axis goes up to 36. If I then
> change the period to 60, then the same chart alters its x-axis and plot
> to show figures upto 60...
>
> Can this be done...
>
>

James (and Mattgoof):
There is a more elegant solution, called "dynamic named range", that works
with the OFFSET command. Search the web with this jargon, or check out Jon
Peltier's website; it contains links to many others where you find
instructions how to do it.
[http://peltiertech.com/Excel/Charts/...artLinks.html]
HTH,
Henk

"mattgoof2005" wrote:

> I'm in a similar situation. I simply made the chart to show the longest
> period possible. Most people don't complain about half the chart being
> empty. I just filled the rest of the chart if if statements. Modified for
> you, just use =IF(Duration<={Corresponding Period},"",{calculation}. The if
> on the data keeps a ton of 0s from showing on the graph. This isn't perfect,
> but it works for me.
>
> "[email protected]" wrote:
>
> > Hi,
> >
> > I have a spreadsheet that works out loan interest and cumulative
> > interest.
> >
> > I enter the loan amount, APR and period in Cells A1, A2 & A3
> > respectively.
> >
> > Then, in a table below (using IF statements) the data is filled out.
> >
> > i.e. if I put in a period of 36months, the period column fills out
> > until it counts to 36.
> > (If I put 60 months, it counts until it reaches 60 etc.etc.)
> >
> > I would like to construct a chart that displays the prinicipal of the
> > loan that is paid, the cumulative interest that is paid, against a
> > 'running' x-axis based on the period.
> >
> > Here's the tricky bit... I want the chart to auto update when the
> > period is changed.
> >
> > i.e. if period = 36, then the chart x-axis goes up to 36. If I then
> > change the period to 60, then the same chart alters its x-axis and plot
> > to show figures upto 60...
> >
> > Can this be done...
> >
> >

For a variety of examples see
Dynamic Charts
http://www.tushar-mehta.com/excel/ne...rts/index.html


--
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...
> Hi,
>
> I have a spreadsheet that works out loan interest and cumulative
> interest.
>
> I enter the loan amount, APR and period in Cells A1, A2 & A3
> respectively.
>
> Then, in a table below (using IF statements) the data is filled out.
>
> i.e. if I put in a period of 36months, the period column fills out
> until it counts to 36.
> (If I put 60 months, it counts until it reaches 60 etc.etc.)
>
> I would like to construct a chart that displays the prinicipal of the
> loan that is paid, the cumulative interest that is paid, against a
> 'running' x-axis based on the period.
>
> Here's the tricky bit... I want the chart to auto update when the
> period is changed.
>
> i.e. if period = 36, then the chart x-axis goes up to 36. If I then
> change the period to 60, then the same chart alters its x-axis and plot
> to show figures upto 60...
>
> Can this be done...
>
>

Thanks to Henk and Tushar...

Named groups works fine with the OFFSET function. (insert - names -
define).

Thanks.

Jamie