Plotting age along a horizontal axis in graph

[neilholmes]

Hi everyone,

I am hoping that someone can help me.

I am new to the forum, and have an problem with a spreadsheet I am creating for my work.

I am currently creating a cash-flow modelling tool within Excel, analysing fund values based on certain assumed growth rates. I have created a bar chart to show the value of fund increasing each year, (easy stuff).

The spreadsheet calculates a starting point on the horizontal axis on an individuals current age, by entering a date of birth, and then displays the graph from that age onward. I would ideally like the graph's horizontal axis to stop at age 100, no matter the individuals current age.

For example, for a 40 year old, the graphs horizontal axis will show 60 years to age 100, at 50, the next 50 years, etc.

At the moment the chart runs for a preset number of rows, only 1 > 100 for example. To show to age 100, I have to manually adjust the row to stop at age 100. This is not ideal as the chart has multiple inputs and therefore each one needs to be adjusted every time.

Is it possible, to set the horizontal axis within an excel graph to stop given a cells value, i.e. to 100. The graph would then only show the corresponding number of bars between current age up to 100.

Hope that someone can help.

Many thanks in advance.

- Neil

[MrShorty]

It sounds like you want a "dynamic chart" using a "dynamic named range". Most example use the OFFSET() function, and most examples are built on a COUNT() function (counting the number of filled cells). In your case, you might use a COUNTIFS() function (to count how many entries are below 100) or a lookup function (where is the 100 entry in the range), but the overall idea should work.

If you are unfamiliar with dynamic charts and dynamic named ranges, there are many tutorials on the internet.
https://www.excel-easy.com/examples/...med-range.html
http://best-excel-tutorial.com/56-ch...-dynamic-chart
https://chandoo.org/wp/dynamic-chart-data-series/
or put "dynamic chart excel" into your favorite search engine.