Thread: Plotting the Forecast()

I have a dynamic line chart. The user can choose to display the past 7 days, past 30 days, past 90 days, or past 365 days. If 7 days are selected, I calculate and display (in an adjacent cell) the +7 day forecast. If 30 days are shown, I calculate and display the +30 day forecast, etc. I also calculate the average for each line, and display in another adjacent cell.

I'd also like to display these points on the chart. If I chart them as individual series, they all appear on the left-hand y-axis, since there's only one data point to plot. This is fine for the average, but I'd prefer to move the forecast to the right-hand side of the chart, since that is the direction of the 'future'. I'm sure I could populate a column of "zero's" or "NA()"'s for each series and insert the forecast in the appropriate cell (7th, 30th, 90th, or 365th) and plot the result, but that seems like an awful lot of wasted spreadsheet space. Is there a way to use a named array in the SERIES function to dynamically create this data series? I feel I'm close to the solution, but after a few days I've hit a brick wall.

ZoomVal2 is the number of days on the x-axis (7, 30, 90, or 365). ForecastCell is the value of the Forecast. The following function returns the data I want plotted (6 zero's and the forecast, 29 zero's and the forecast, etc.) seperated by commas. I just can't figure out how to get the SERIES plotted.

=REPT("0,",ZoomVal2-1)&ForecastCell
returns "0,0,0,0,0,0,81" when ZoomVal2 is 7 and Forecast is 81, and works correctly when the other 3 options are selected.

I've purchased John Walkenbach's "Excel Charts", Charles Kyd's e-Book, and search other forums, with no luck.

Please forgive the lengthy post, and thanks in advance for any help offered. I'm new to arrays, so please use little words

Jeff

My first idea is that ZoomVal2 does not have fixed cell notation e.g. $A$4. Therefore ZoomVal2 references to different ranges for each cell in the worksheet and also for the cell where you use =REPT("0,",ZoomVal2-1)&ForecastCell.

If this was done correctly then an example workbook would be nice.

Just for the SERIES. normally this looks like:
=SERIES(,,Book1!RedForecast,14) or
=SERIES(,,Sheet1!RedForecast,14)

For the rest i would be very helpfull if you upload your data (workbook) so we can have a go with it.