Not including blank data in charts

Hi all,

I'm trying to chart some data from workings I have generated. I wish to keep the same chart (and data source range) should I change certain assumptions of the variables for ease of reference.

Essentially I wish to show the total value of investments for a person for the period up to when they hit 65 (retirement age). I do not wish to show any data beyond the age of 65.

Is there a way that I can limit the chart to display data to reflect this? e.g. if someone is 55 now I wish to only show 10 years of projections and the chart to cut-off at 65, but if I change the date of birth to 1982 the chart would display 35 years of projections?

The attached shows roughly what my problem is. At present the chart is reflecting the blank cells as a value of 0, whereas I don't wish to see it in the chart if the value is blank.

Any help greatly appreciated.

P.s. I have included a formula in each of the cells to return a blank cell if the age goes over 65. The chart is based off data from cells A5:C52.

Hi

have a look at this page

There are many others on the same topic around

Thank you.

So basically my best strategy is to go with the NA() route which will make my chart as correct as Excel will allow.....but will admittedly make the data table it's built from to appear unsightly?

I'm surprised that Excel has not overcome an issue like that, quite a design flaw of the program if so.