Aligning multiple graphs

[F*SH]

Hi all

Nice site, I'm like the all the stuff you got here!

Anyway, an interesting problem to tickle your brains:

I've got a bunch of charts on a page that I want vertically aligned
(i.e., the vertical axes line up when the graphs are stacked down a
page). I thought this would just be a matter of setting the chartarea
object in a fixed position. Which they are; the chart objects all line
up perfectly.

The problem is, as I see it, is that the amount of text on the vertical
axis is pushing the plot area in. I got rid of everything shy of the
scale, which I need, but this varies (sometimes it might be double
digits, other times 3 or even 4 digits)

In a cheap attempt to elicit some ideas, I decided to post a picture of
my woes. Everyone likes a picture!

''
(http://img84.imageshack.us/my.php?image=graphmisalignment5on.png)

Here, I have a typical situation. Two graphs, with their chartareas
both identical in size and horizontal position. But the y-axis label
text on the side of the plot pushes the plotarea in. This wouldn't
normally be an issue, but, depending on the number and type of digits
in the text, the x-axis is shifted over by a different amount for each
graph. I really need these to all be aligned perfectly as they share a
common x-axis along the bottom (not visible).

Any ideas how to get around this?

Plotarea.left seems to have no effect whatsoever, plotarea.insideleft
is read-only, and implementing VB code to set the number of significant
figures runs into a whole plethora of problems.. I'm really not a fan of
charts in Excel, they just don't behave

Thanks!

 

[Kelly O'Day]

F*SH:

Good question. I run into exactly the same situation.

I have developed panel charts as a solution. Take a look at this page to get
some ideas.

http://processtrends.com/toc_panel_charts.htm

Kelly

(e-mail address removed)

 

[F*SH]

Wow, that's a good-looking chart set you've built there. Some problems I
have though: first, these charts of mine are being added by a VB script.
Transforming all that data and working out good parameters is a tedious,
albeit possible, process - I guess what I was hoping for was a one-line
command to align the axis. The other issue is that I need each series
to be in its own graph. The reson for this is that the user needs be
able to move charts around, to compare to variables side-by-side (at
the moment there are far too many graphs, even for one page! We're
talking 40-50 here ).

Your underlying concept (an elaborate graph-combination), while very
impressive probably won't work for me.

1. Y Axis Labels number of characters must be the same for all charts
...
If you need to chart data with different number formats, you can solve
the alignment issue by adjusting the number of decimals for each chart
so that all charts have the same number of characters in the Y Axis
Labels.

This is exactly the process I need to fix my problem, but I think it's
easier said than done! Any idea how this was intended to be executed?
One of my earlier attempts, which involved setting all the labels to 5
significant digits failed, for three reasons: first, decimal points,
not considered digits, shifted charts with decimal labels. I could live
with this though, as it is only a pixel or two. The other problem is
that is causes lots of redundant zeros to appear. Again, I could live
with this, perhaps changing them to whitespaces. Lastly, I need to
calculate the maxima, minima and ideal step myself, which is one of the
one things Excel charts do well..

I just wish that plotarea.left worked... any idea why it wouldn't? It
seems like such a simple solution.

 

[Tushar Mehta]

You raise a lot of interesting issues some of which I will, unfortunately,
ignore since addressing everything would make this reply very long.

As far as your last point about adjusting PlotArea.Left goes, aligning
multiple charts is always a bit iffy but you can try the following:

What you really need to do is align PlotArea.InsideLeft. Unfortunately,
InsideLeft is a read-only property. But, you can tweak the Left properties
of the different charts until their InsideLeft values are the same.

You may also want to consider an option that if I were in your shoes I would
probably implement.

Instead of 40+ charts, create just a few. How many is a "few?" Whatever
fits on the monitor and whatever makes your users comfortable. Now, for
each, provide a drop-down list to select which of the 50 actual variables
should be shown in this chart. This would be similar to example 4 in
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

--
Regards,

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

 

[F*SH]

Hi Tushar

Thanks for your reply. I'm starting to agree that this isn't a
straightforward as I was hoping!

Now, normally what you suggest about having fewer graphs makes sense
However, in this application, the tool is there to present 30-4
variables simultaneously. Basically, the user is looking for an
correlation between the arefacts on each graph. When they're al
stascked above each other,m this is easy; but if you need to select th
graphs to display, it becomes much harder. You'd need to check eac
graph with each other one and I'm sure you're aware, that's quite a fe
permutations!

I like your idea to use InsideLeft as a 'aligned' flag. I'l
investigate it further - although in my experience the only thing tha
modifies the left padding is the plotarea.left property, and this doe
so very erratically. It sometimes moves the border, othertimes not. Bu
at least I can know if it's worked.

Cheers

 

[F*SH]

A qualified success!

I got the graphs working with the code below. They align their axe
perfectly and are within 2 pixels of being the same size. Best I ca
do. The disadvantage is that, because after each one of the hundreds o
resizes performed, the graph is redrawn, which results in a slow (bu
fun-looking) execution.


Code
-------------------
' *** WIDTH

ChartObjects(1).Chart.PlotArea.Width = 10
For i = 0 To 300

Range("A2").Value = i
ChartObjects(1).Chart.PlotArea.Width = ChartObjects(1).Chart.PlotArea.Width + 1

If (Abs(ChartObjects(2).Chart.PlotArea.InsideWidth - ChartObjects(1).Chart.PlotArea.InsideWidth) < 2) Then
Exit For
End If

Next i

' *** HORIZONTAL OFFSET

ChartObjects(1).Chart.PlotArea.Left = 10
For i = 0 To 100

Range("A1").Value = i
ChartObjects(1).Chart.PlotArea.Left = ChartObjects(1).Chart.PlotArea.Left + 1

If (ChartObjects(1).Chart.PlotArea.InsideLeft = ChartObjects(2).Chart.PlotArea.InsideLeft) Then
Exit For
End If

Next