Month and YTD charts

[OldSchoolBug]

Management are wanting to see four data series plotted on one chart by month
firstly a clustered column chart showing month actuals & budget, and then plot
the YTD cumulative position of actuals and budget as a line chart on the same
chart using a secondary axis to plot these lines due to the higher values shown
in the scale. The 'classic combination chart' that's the easy bit.

Excel has plotted the line chart on the second axis but the lines 'overlay' the
columns as you would expect, but management want to see these lines above the
columns, thus creating a new zero value x axis above the column chart. The
quickest way I found to do this is set the secondary axis scale to start at a
negative value so the zero value is above the columns. It is a bodge and I
don't like it, showing negative values just confuses the issue.

The only other solution I have been experimenting with is two seperate charts a
column and a line and pasting the line chart over the column as picture. This
is kind of working only problem is resizing the charts and getting the
positioning perfect.

Is the chart possible??

Thanks,
Paul
PS sorry for long post

 

[Tushar Mehta]

No need to apologize for the post length. Better than too short a
post. <smile>

Along the lines of what you are currently doing, see the
Excel/Tutorials/Stacked Charts page of my web site. You don't have to
create / paste a picture. However, you do have to size and align the
charts.

An alternative would be to create dummy series that is vertical line.
Use it in place of the secondary y-axis. Create the desired labels in
worksheet cells and use Rob Bovey's XY Chartlabeler or John
Walkenbach's Power Tools to add the labes to the dummy series.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Paul -

In addition to Tushar's suggestions, you could try adjusting the
secondary axis this way. To hide the negative values which further
confuse your management, use a custom number format which does not
display the negatives. In general, a custom number format has four
parts, separated by semicolons: the parts are for positives, negatives,
zeros, and text. To hide the negatives, put something in the negative
section of the number format that won't show the number, like one of
these examples:

0;;0;
0.00;;0.00;
_($* #,##0_);;_($* "-"_);

Your left hand axis will have numbers going higher than your secondary X
axis. You can change the number format so that the sections stand for
other than positive, negative, etc. Put something in square brackets to
identify your condition. The following will use " " to display anything
greater than 4000:

[>4000]" ";0;0;

You can use >, <, >=, <=, <>, and = in your condition.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______