If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#3
|
|||
|
|||
Aligning multiple graphs
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 |
#4
|
|||
|
|||
Aligning multiple graphs
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 |
#5
|
|||
|
|||
Aligning multiple graphs
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 |
#6
|
|||
|
|||
Aligning multiple graphs
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Create multiple graphs on seperate page | IronMo | New Users | 0 | April 17th, 2006 10:33 PM |
Multiple Choice of the Multiple Choice | Debra Ann | Using Forms | 2 | March 10th, 2006 02:06 PM |
Multiple Graphs | Ross | General Discussion | 2 | November 23rd, 2005 05:32 PM |
How to Run Multiple PowerPoint Instances | Drew Lettington | Powerpoint | 1 | September 17th, 2005 04:13 AM |
Multiple docs open multiple words | Ron Smith | New Users | 2 | December 11th, 2004 09:21 PM |