+ Reply to Thread
Results 1 to 6 of 6

Scaling 2 charts together

  1. #1
    Forum Contributor
    Join Date
    06-29-2008
    Location
    Grand Rapids, MI
    MS-Off Ver
    Office 2010
    Posts
    127

    Scaling 2 charts together

    This one might not even be possible.

    I have a line of data, and when graphed together, (using a line graph) the data is consistent.

    However, for printing purposes, i need to break it into 2 graphs...

    the issue here... excel scales them based on their highest values... which makes the data look different than it should.

    I am trying to get the charts to look like the same chart, just broken in half.
    Attached Files Attached Files
    Last edited by robert_shindorf; 12-19-2008 at 04:12 PM.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372
    Hi,

    it sure is possible. I'm on 2003 but the same principle should apply to 2007.

    first you have to temporarily display the Y axis on all your charts, so you can format it.

    Then, format the axis of each chart to have the same minimum and maximum value. It might be a good idea to open the combined chart, take a note of the values there, then apply these values manually to the two other charts.

    after that, you should be able to hide the y axis again, and the scale should stick.

    HTH

  3. #3
    Forum Contributor
    Join Date
    06-29-2008
    Location
    Grand Rapids, MI
    MS-Off Ver
    Office 2010
    Posts
    127
    ok, now here's the trick, (first, i've never set a value for an axis). can it be based on a formula? the values could be anywhere from 0 to 500,000 and it's very important that the pattern be seen.

    My thoughts are that I could make a formula find the max value and add ten percent.

    hopefully that would work, and then I could link both charts values to that cell.

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372
    Just rambling here, not tried and tested:

    you could add a second data series to each chart and format the line to be white or the same colour as the background, so it effectively becomes invisible.

    The data for the series you would calculate to be the max value of your data plus a percentage of that max value. This should not be too hard to do.

    The invisible data series may only need one data point in order to influence the y axis. Include the data series in both charts and they should both have the same scale.

    Hope that gives you some ideas to play with.

  5. #5
    Forum Contributor
    Join Date
    06-29-2008
    Location
    Grand Rapids, MI
    MS-Off Ver
    Office 2010
    Posts
    127
    i took that idea of yours about ploting another line, and then used my idea of a max value... and came up with this solution... i think it works, let me know if you believe it could be improved on.
    Attached Files Attached Files

  6. #6
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372
    Yep, that was the idea.

    You could reduce the second data series for each graph to just one data point. This saves a few nanoseconds in calculations. If you format the series to have no border and no shading, then it becoms practically invisible, but you could also play with the formatting to create background effects.

    cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1