# Dynamic graph

#### SamS

##### Well-known Member
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.

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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

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

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

Replies
2
Views
257
Replies
0
Views
884
Replies
5
Views
3K
Replies
7
Views
598
Replies
4
Views
994

1,221,508
Messages
6,160,222
Members
451,631
Latest member
coffiajoseph

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back