Column chart - data labels problem

[alanbr]

I'm developing a chart for a client who has very specific
requirements. The data is of the following type of info:

Q1 2009 Q2 2009 Q3 2009 Q4 2009 YTD 2009
Alpha 3.3% 53.3% 18.8% 9.5% 105.8%
Bravo -0.2% 12.2% 19.7% -22.5% 3.9%
Charlie 106.6% 125.6% 2.3% -1.5% 368.4%
Delta -8.3% 1.4% 0.9% 0.8% -8.1%

I need to lay out the chart with the Y-axis being the % and the X-axis
being the Quarters, etc.

So far so good - I can do that. But the client wants data labels
showing the values immediately *above* each column (regardless of
whether the column is above or below the x-axis) - I have no problems
with the positive info but how do I show the negative info (eg -8.3%)
as a column *below* the x-axis with the data label (showing -8.3%)
*above* the x-axis?

Any help will be gratefully received.

 

[Andy Pope]

You can add a dummy set of series. These plot the negative values a
positive values. The rest are plotted as zero.
Move the dummy series to the secondary axis. Delete the secondary
axis so they share the primary axis scaling.

Apply data labels to all series.
Custom number format for the series on the primary axis so that onl
positive and zero are displayed.

0.00%;;0.00%

Custom number format for the series on the secondary axis so only th
positive values are displayed with a minus sign

-0.00%;;

Set the data label alignment for the series on the secondary axis t
Inside Base.


+-------------------------------------------------------------------+
|Filename: 182912.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=445|
+-------------------------------------------------------------------+

 

[alanbr]

Hi Andy

First off, thanks so much for replying so quickly!
Secondly, I did what you said and it worked up to a point - the
problem being that
the secondary axis has a different scale to the primary axis.
I only realised this after I deleted the secondary axis and finished
all the steps.
This chart (or rather the source data) is going to be populated from a
database via VB and then
sucked into a Word report.

How can I (via macros if necessary) ensure that the secondary axis and
primary axis are the same scale?

Thirdly, I clicked on the Download link on your reply, thinking it
might help even more but got blocked 'cos
of permissions.

Thanks very much for your help so far.

Regards
Alan

 

[Andy Pope]

Hi,

Yes you probably need to be a member of CodeCage to see the attachment.

For me deleting the secondary vertical axis causes the series on the
secondary axis to use the primary axis scaling.

To make the secondary Y axis match the primary Y axis the simplest thing
is to add another dummy series to the secondary axis. This only needs 2
values, the MIN and MAX values from the source data. Change the chart
type to line instead of column. Format the line to have no marker or
line formatting.

Cheers
Andy

 

[alanbr]

Hi Andy

That worked a real treat!! Thanks so much.

Regards
Alan