Dynamic chart help

[Ralane]

Hi all

I'm trying to create a graph based on a data series which contains 45 points.

I want the graph to be able to update itself to show the amount of data points based on a value in a cell. For instance if I wanted to see only the first 20 of the 45 points, the graph would look to the number '20' in a different cell and show that many points in the graph.

I'm not sure if this is much easier than I'm making it, however I feel like I should be using the OFFSET function. I'm not sure if there's a better way of doing this though as most of the articles I've read only have OFFSET showing the last x amount of data points in a series, or update the graph when the series has more points added to it. Can anyone offer some advice?

To reiterate, my series will always have 45 points, but I want the amount of points charted to change based on the value in another cell.

Thanks in advance!

Ralane

[Glenn Kennedy]

Is this the sort of thing you had in mind??

[Ralane]

Hi Glenn

Thanks for your reply.

I think your example is doing something different to what I'm trying to achieve. I'm not familiar with the method you've used, but it looks to me like your solution takes the number of points given in the relevant cell, and plots them equally spread across the entire date range i.e. Jan 12 - Nov 14?

This does look useful, but what I'm trying to do is plot increasing/decreasing values over a maximum time period of 45 years. My calculator I'm using for the series data will always be 45 rows long, but I'd like the number entered in the referenced cell (e.g. your 'Points' cell) to provide the series data for the graph. For instance, if I was to enter 22 in the referenced cell, I'd like the data for the next 22 years from 2018-2040 to be used as the series data for my graph and the remaining 23 years of data i.e. the data until 2063, to be ignored in the series.

Hopefully I've explained that well enough, I'm quite a novice at this sort of function. Greatly appreciated!

[Glenn Kennedy]

Try this, as an alternative...

[Ralane]

Hi Glenn

That looks to do exactly what I'm looking for! Thank you so much for the help and quick reply!

Ralane

[Ralane]

I've used Glenn's solution here and it has worked well.

I'm having an issue trying to stop the graph plotting '#N/A' points however.

Whilst this isn't too great an issue, when trying to plot two different data series together which have highly differing max/min values, I thought an additional axis would be useful to display this better. I tried using a 'combo' graph as in the attached image and the N/A plot points reappeared

Is there a way to stop the graph plotting whilst maintaining the automation of the dynamic plotting?

Kind regard

Ralane

Combograph.JPG

[Glenn Kennedy]

The image didn't attach. In any event, it's non-editable and hence useless. A sheet would be better!!

The reason for putting the #N/A error is that excel ignores them. So your problem lies elsewhere...

[Glenn Kennedy]

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Ralane]

Hi Glenn

Please find an example spreadsheet attached

The data set contains the points I'd like to plot. As you can see the x-axis contains N/A errors and still plots them.

Thanks for the help again

Ralane

[ShelbyMan]

Hi Ralene,

See if this works for you....I am no expert on the graphs as I am just dipping into that area.
I have added a IFERROR wrapper to your formulas to your Age, Income & Value after Income dataset. This will remove the NA's for your dataset & Chart.

I then added named ranges for the Age, Income & Value ranges that will exclude the appears to be blank cells in your dataset but aren't because a formula resides in the cell.
This will make the right side of the chart not have a blank area.

Note: Excel is crashing when I exit this file so I save it first then exit. I am not having issues with my other files though.

Please backup your file first!

[Ralane]

Hi ShelbyMan

That appears to have solved my problem, thank you very much! I had tried using an IFERROR but didn't think about named ranges.

Thanks for your help and thanks again to Glenn for his solution. This will do exactly what I've got in mind

Ralane