Progressive progress charts 1

[StephenA]

I have a chart with months on X axis

On the Y axis are monthly values which are generated by a formula.

I want to insert the formula for the whole year but truncate the chart at the latest month. i.e. At end of July the chart should only show values to end July but the formula would extend to the end of the year (showing blanks).

At the moment the chart line dives down to zero for August.

I have tried if statements and inserting "-" for zero values but that did not help.

Without deleting the formula is there a simple way of truncating the graph?

Stephen Argles
Land & Marine

http://www.landandmarine.com

 

[MintJulep]

Just set the max value for the X-axis. Right-click on the axis - it's on one of the tabs.

 

[StephenA]

MintJulep,

Thanks for the suggestion but I was hoping to have the X axis read January to December all the time and I also have 20 graphs so I wanted to avoid changing them each month.

Stephen Argles
Land & Marine

http://www.landandmarine.com

 

[Panars]

You were on the right track with using zero or "-", but try using NA() in the formula for future dates. Excel should not chart NA() values.

 

[MintJulep]

Or using named ranges.

 

[StephenA]

Panars

Perfect solution.

Thank you

Stephen Argles
Land & Marine

http://www.landandmarine.com