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...

Dynamic Charting - i think!
 

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.

" 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...

Dynamic Charting - i think!
 

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.

" 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...

Dynamic Charting - i think!
 

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 . com,
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...

Dynamic Charting - i think!
 

Thanks to Henk and Tushar...

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

Thanks.

Jamie