Dynamic graph

SamS

Well-known Member
Joined
Feb 17, 2002
Messages
542
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

Well-known Member
Joined
Feb 17, 2002
Messages
542
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
 

Forum statistics

Threads
1,141,019
Messages
5,703,766
Members
421,315
Latest member
awaisnazir139

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top