Dynamic graph

[SamS]

I have a several graphs that chart a number of series eg Budget, Actual and Forecast.
The forecast is an extension of the Actual to date plus ongoing budget.
I need to have it displayed as 3 series in each graph.
The tricky bit is that the Actual series is to chart the months up to and including the last month eg August and the Forecast is to chart from September onwards ie will appear as 1 series but the Actual will be a solid line and the Forecast a dotted line.
The spreadsheet would have the month in a nominated range.
I have tried having the data for the Actual and Forecast on 2 separate lines and can use formula to start and finish the range using #N/A to force a null result where necessary but unfortunately the people who use these don't like it. I do have charts that work on dynamic ranges but this one is a little out of my depth.

Any help would be appreciated.

 

[SydneyGeek]

Hi Sam, one possible approach:

This uses months of the year for the X-axis, covering one calendar year. Adjust to suit.

C2 contains the current month number.
B7:D7 are the headings -- Actual, Forecast, Fcast, Act
A8:A19 are the numbers 1 to 12.
B8:B19 is Actual data
C8:C19 is Forecast data
D8 is =IF(A8>$C$2,C8,B8) [Fill down to D19]
E8 is =IF(A8<=$C$2,B8,#N/A) [Fill down to E19]

To get a sensible X-axis, create a list of month names in F8:F19 and point the data series at these values.

Plot the chart using D8:D19. I chose a Line chart (sounds like what you need). Format this to look like the Forecast data -- dotted line.
Select the data from E8:E19, drag and drop onto the chart. You now have Actual data overlaying Fcast. Format this line to suit.

By changing the value of C2, the chart should adapt.

I know your users don't like the #N/A trick but it's the only way I know to stop the chart misbehaving. You could apply a conditional format to the column and hide #N/A, if it offends them :wink:

Denis

 

[SydneyGeek]

Just had a thought -- you could turn the Actuals into a dynamic range (Column B, in my example) and plot that instead of the #N/A version.

You mentioned before that you had worked with dynamic charts before so I will assume that you know how to do it. Otherwise check out Tushar Mehta's or Jon Peltier's sites for plenty of charting gems.

Denis

 

[SamS]

Just had a thought -- you could turn the Actuals into a dynamic range (Column B, in my example) and plot that instead of the #N/A version.

You mentioned before that you had worked with dynamic charts before so I will assume that you know how to do it. Otherwise check out Tushar Mehta's or Jon Peltier's sites for plenty of charting gems.

Denis

Thanks Denis - will give it a go - there are about 50 lines of data that needs to have this event occur.

cheers