Autoscaling (with fixed range) the x-axis of a line chart?

[excelswimmer]

Hi I'm a bit confused on this one...

In Excel 2007, I am using a trendline on a "Line with Markers" chart. I have Date on the X-axis and Weight on the Y-axis.

I'd like to do a 1-year projection from the last date where there's a Weight value plotted on the chart.

For example, if I have several Weight values beginning from 6/1/16 up to the Weight value for today (9/2/16) plotted on the line chart, I want the chart's maximum x-axis value to be 9/2/17. (1 year later) The trendline goes all the way from 6/1/16 to 9/2/17.

Then when I input a new weight value a week from now (9/9/16), the chart's maximum x-axis value now becomes 9/9/17. The trendline now goes all the way from 6/1/16 to 9/9/17.

So there's always a 1 year projection ahead of the last Weight value on the chart.

In Excel 2007, I only see under Axis Options (for the horizontal axis) that 'Maximum' can be set as Auto or Fixed. Auto will automatically update the maximum date but doesn't allow setting a fixed range ahead of either first or last plotted date, and Fixed requires a fixed date (not range). Is there a way to set the x-axis range from a plotted date, in the manner I just described? (without resorting to VBA, thanks)

[MrShorty]

The only built in options for axis limits are "auto" and "fixed". There is no built in option for "auto with a different algorithm than the native Excel algorithm" (though it can be done with VBA if you are willing to relax the no VBA constraint).

We might require a better description (maybe even a sample file) for what you have. Are you using the chart trendlines to create the trendline? If so, and you select the "forecast forward 365 periods", Excel should automatically extend the trendline and the axis (assuming auto is selected) forward one year.

If you are creating the trendline in the spreadsheet, then you should have x axis values that extend forward that year, and Excel's auto axis should extend the axis to or beyond the main data series.

[excelswimmer]

I am using the chart trendline (set as Linear), with 366 forward forecast periods for trendline and the horizontal axis 'Maximum' set to Auto.

The "forecast forward" method doesn't always work out as planned and may project ahead several years instead of just a year. It usually does a year with above settings, though.

I'm thinking about maybe trying the VBA method, but have two questions first.

The reason I didn't want to use VBA is because last time I used VBA, I started to get a persistent nagging "Do you want to save changes" prompt every time I tried to close my Excel spreadsheet. Even when all I did was open the file then try to immediately close it.

Does this always happen with VBA? And for VBA to be used, all users must enable/allow the use of macros?

The spreadsheet I created is my biggest Excel project ever and it involves several other things. I'm not sure if I can isolate the chart easily to create a sample file, but I hope I've been descriptive enough.

[MrShorty]

The "forecast forward" method doesn't always work out as planned and may project ahead several years instead of just a year.

Does it actually project the trendline out multiple years, or are you only referring to the x axis? I would expect it to always limit the trendline to one year, though the default axis may choose to extend to several years, depending on the date range being covered.

The reason I didn't want to use VBA is because last time I used VBA, I started to get a persistent nagging "Do you want to save changes" prompt every time I tried to close my Excel spreadsheet. Even when all I did was open the file then try to immediately close it. Does this always happen with VBA? And for VBA to be used, all users must enable/allow the use of macros?

I don't that it "always" happens, but it frequently happens. There are methods/commands that can block that warning, if you want to learn them.

Yes, anyone who wants to use those procedures as part of the spreadsheet must enable macros. It is possible to open a macro enabled spreadsheet without enabling macros, but any functionality (like automatically adjusting chart axes different from the built in algorithms) will not be available.

If you want to explore how to do this in VBA, here's a good tutorial on linking chart axis limits to cells. Even if you end up doing something different, it should show you how to use the different chart properties to modify the chart. http://peltiertech.com/link-excel-ch...lues-in-cells/

[excelswimmer]

Does it actually project the trendline out multiple years, or are you only referring to the x axis? I would expect it to always limit the trendline to one year, though the default axis may choose to extend to several years, depending on the date range being covered. [/url]

Sorry it took me several days to reply.

As for your question, I'm not sure I fully understand. The trendline is always as long as the x-axis itself, based on what I've seen. What I was describing is that in some cases the x-axis will auto-change from displaying in terms of days, to displaying in months instead - especially when two manually input (and next in the series) x-axis values are a month apart (instead of a day apart). This also causes the trendline to extend over several years (instead of one year).

Anyway, I figured out how to make it work as I wanted - and VBA is not needed. I simply didn't realize the connection between "base unit" in Axis Options for the x-axis AND forecast forward for the trendline.

My base unit was originally set to Auto, and that's what caused the problem. Once I set it to Fixed, the x-axis no longer auto-changed from days to years. And, in that case, the trendline always stays one year ahead of the last plotted x-axis point.

Thanks for your help!

[MrShorty]

Ahh, that makes sense. Glad you figured it out.