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
(E-Mail Removed) wrote:
> 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
Similar Threads
