Negative #'s on the wrong side

  • Thread starter Thread starter Smitty
  • Start date Start date
S

Smitty

Win XP, Office XP

I've created a horizontal bar graph starting with
negative 30 (-30) up to positive 30 (30). My problem is
that while all almost all the negative data labels are on
the left side of the bar (correct), if it's -3 or less,
it puts the number on the right side of the bar --
displays it on the positive side, which is wrong. How do
I fix this?

Any help appreciated!! (example below)

-20 |------||
-25|----||
||----|20
|-||-2
 
Win 2k, Office 97 (yeah, really! My employer's not into spending money.)

I made a chart such as you describe, and for any negative values, no
matter how small compared to the range, the labels were to the left of
the bar. If the value changes to a more negative number, does the label
go back where it "belongs"?

All I can suggest is to try adjusting the data label alignment. Select
the data labels (one single click), then the offending label (a second
single click), and press Ctrl-1 (one) to format it. On the alignment
tab, make sure the Outside End label position is selected.

- Jon
 
The labels don't "go home" where they belong unless I
increase (or decrease, depending on your point of view)
the number dramatically. I guess when you're that tiny,
the left side is scary!

Actually, I already tried every alignment option Graph
has. Nada. I did some more checking, it looks like it
is actually a problem with Graph itself, not just inside
Excel. Created the same type of graph in Word, same
thing.

More info: my graph is only 1.5" wide, but the scale
goes from -1000 to +1000. The scale has to be that
gigantic to fit all the data labels on the end, not
squished into the bar. Maybe try that and see if yours
does that. . .? Heeeelllpp, please.
 
Smitty -

I made charts in Excel and in MS Graph (Excel's little brother that is
used to put a chart into Word or PowerPoint). In all cases, no matter
how I squashed the chart or expanded the axis scale, the labels went
where I'd expect to find them, beyond the ends of the bars, left or
right. I can't think of a reason you'd be having the problem, which
isn't denying it's there. Excel is like that.

- Jon
 
Could I send you an example file? Or would that not work
because I'm a newer version than you. . . (probably not,
but it's worth it to me to ask.) Thank you very much for
trying to help.
 
I can't promise I'll get to it promptly, but send it along. Make sure
you make the obvious fixes to my email address.
 
Hi smitty,

Hopefully this will save Jon some time :)

Try this code, watch out for line wraps.

'---<Code Start>---
Option Explicit
Sub FlipNegLabels()
Dim intPoint As Integer
Dim objSeries As Series
Dim sngAxisLeft As Single
Dim sngXMin As Single
Dim sngXMax As Single
Dim sngWidth As Single
With ActiveChart
sngXMin = .Axes(2).MinimumScale
sngXMax = .Axes(2).MaximumScale
sngAxisLeft = .PlotArea.InsideLeft + ((.Axes(2).CrossesAt -
sngXMin) * (.PlotArea.InsideWidth / (sngXMax - sngXMin)))
For Each objSeries In .SeriesCollection
For intPoint = 1 To objSeries.Points.Count
If objSeries.HasDataLabels Then
If objSeries.Values(intPoint) < 0 Then
If objSeries.DataLabels(intPoint).Left >
sngAxisLeft Then
sngWidth =
GetDataLabelWidth(objSeries.DataLabels(intPoint), ActiveChart)
objSeries.DataLabels(intPoint).Left =
..PlotArea.InsideLeft + ((objSeries.Values(intPoint) - sngXMin) *
(.PlotArea.InsideWidth / (sngXMax - sngXMin))) - sngWidth
End If
End If
End If
Next
Next
End With
End Sub
Function GetDataLabelWidth(DLabel As DataLabel, MyChart As Chart) As Single
'
' Determin label width by try to move label off of the chart area.
' This is not allowed But the label is moved as far as possible.
' use the final position - requested position to approximate a width
'
Dim sngMax As Single
sngMax = MyChart.ChartArea.Width
DLabel.Left = sngMax
GetDataLabelWidth = sngMax - DLabel.Left
End Function
'---<Code End>---

Cheers
Andy
 
I looked at Smitty's charts offline, and had the same problem, which I'd
never seen before. The charts were about 3 cm wide and almost a portrait
page tall, but even when I changed the aspect ratio, the labels were on
the wrong side. I suggested removing the data labels on the bars, and
adding two dummy XY series, one for positive values and one for
negatives, using my conditional charting approach (see my sig line for
the web site). Getting the points to match up with the bars required a
dummy Y value, from 1 to the number of bars, and I had to fiddle with
the secondary axes. I added labels to these, using Rob Bovey's XY Chart
Labeler (a free addin from http://appspro.com), negative labels to the
left, positives to the right (this is why I added two dummy series).
Then I formatted the extra series to be hidden (no markers and no
lines). I haven't gotten Smitty's feedback on the technique.

Andy's approach works as well, as long as you remember to rerun it when
the data changes.

- Jon
 
Back
Top