Dynamic Charting - i think!

[jamie.rowland]

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

 

[Guest]

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

 

[Guest]

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/DynamicChartLinks.html]
HTH,
Henk

 

[Tushar Mehta]

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


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[jamie.rowland]

Thanks to Henk and Tushar...

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

Thanks.

Jamie