Chart label questions

[ryan.fitzpatrick3]

I have three questions about chart labels. I have labels on a bar
chart. I have it programmed where the cell value in D5 is inputted
into the label. First question. On my chart the on primary y axis is 0
and the scale is -.4 to .4, so 0 is in the middle of the chart. (this
is a variance graph) to the right of the 0 is a positive variance and
to the left is a negative variance. Since all my data currently
reflects on the positive side(right side of the chart) the data labels
are on the left side of the 0 or the negative variance side because it
stays out of the way. my question is I would like the data label to
always be on the opposite side of data whether it be positive or
negative. What I did is i created a macro and dragged the label to the
opposite side to get the code. Listed below. I was thinking about a if
statement that if chart series point 8 is > 0 then this code below
then I would want it to go back to its original location if the
variance was opposite. Make sense?

ActiveSheet.Shapes("Label1").Select
Selection.ShapeRange.IncrementLeft 402#
Selection.ShapeRange.IncrementTop -0.75

Second question on the bar chart the labels i have positioned of the
data bars. is there a way to lock in the labels in a "relative"
location whereas if I increase the chart size the labels will always
be over the respective data bars?

Third question. On the chart scale I have a range that changes. I have
a 0 point in the center and would always like the 0 setting on the y
axis to be in the middle so i can show my variances. Is there a way to
make the min setting be opposite of the max setting. i.e if the max
setting was .5 the min would be -.5? This is the only way I know to
keep the 0 in the middle or is there a better way or another way the
scale can be done or keeping the 0 in the middle? Preferably I would
like the scale code. Thanks.

Ryan

 

[Andy Pope]

Hi,

You can solve all the problems with additional data series.

Assuming your data is in A1:B6, where column A is category labels and B
contains the +/- data.
Create 3 more data series to handle Positive and negative labels and
scaling.

C1: =Positive Labels
C2: =IF(B2<=0,B2*-1,-1)
drag C2 down to C6

D1: =Negative Labels
D2: =IF(B2>0,B2*-1,1)
drag D2 down to D6

E1: =Scaling
E2: =MAX(ABS(B2:B6))
E3: =E2*-1

Add all 3 series to the bar chart. We now have to format each series.

Positive labels, remove bar border and fill.
Apply Value data labels.
Change Number format to -General;;0;
Change Alignment of label to Inside Base

Negative labels, remove bar and fill
Apply value data labels.
Change number format to ;0; or ;+General;
(If you use the ;0; you will need to change for decimal values.
Using ;+General; will always place the + sign but handle decimals)
Change Alignment of labels to Inside Base.
You could set the font colour to red for the negative labels.

Scaling. remove bar border and fill. Set series overlap to 100.

You should be good to go. The labels will display the bars value on the
opposite side of the zero axis point. Zeros will be displayed in the
positive side.

The scaling series will force the values around the zero mid point to be
equal.

Cheers
Andy