Aligning multiple graphs

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?im...ignment5on.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!


--
F*SH
------------------------------------------------------------------------
F*SH's Profile: http://www.excelforum.com/member.php...o&userid=36355
View this thread: http://www.excelforum.com/showthread...hreadid=561383

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






"F*SH" wrote in message
...

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?im...ignment5on.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!


--
F*SH
------------------------------------------------------------------------
F*SH's Profile:
http://www.excelforum.com/member.php...o&userid=36355
View this thread: http://www.excelforum.com/showthread...hreadid=561383

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.

On another page on your site, where it described putting each graph in
a cell of its own, there was an issue raised, and it's superficial
solution:
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.


--
F*SH
------------------------------------------------------------------------
F*SH's Profile: http://www.excelforum.com/member.php...o&userid=36355
View this thread: http://www.excelforum.com/showthread...hreadid=561383

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/ne...rts/index.html

--
Regards,

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

In article , FSH.2axxmf_
says...

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.

On another page on your site, where it described putting each graph in
a cell of its own, there was an issue raised, and it's superficial
solution:
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.


--
F*SH
------------------------------------------------------------------------
F*SH's Profile: http://www.excelforum.com/member.php...o&userid=36355
View this thread: http://www.excelforum.com/showthread...hreadid=561383

Hi Tushar

Thanks for your reply. I'm starting to agree that this isn't as
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-40
variables simultaneously. Basically, the user is looking for any
correlation between the arefacts on each graph. When they're all
stascked above each other,m this is easy; but if you need to select the
graphs to display, it becomes much harder. You'd need to check each
graph with each other one and I'm sure you're aware, that's quite a few
permutations!

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

Cheers!


--
F*SH
------------------------------------------------------------------------
F*SH's Profile: http://www.excelforum.com/member.php...o&userid=36355
View this thread: http://www.excelforum.com/showthread...hreadid=561383

A qualified success!

I got the graphs working with the code below. They align their axes
perfectly and are within 2 pixels of being the same size. Best I can
do. The disadvantage is that, because after each one of the hundreds of
resizes performed, the graph is redrawn, which results in a slow (but
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 i
--------------------


--
F*SH
------------------------------------------------------------------------
F*SH's Profile: http://www.excelforum.com/member.php...o&userid=36355
View this thread: http://www.excelforum.com/showthread...hreadid=561383