+ Reply to Thread
Results 1 to 11 of 11

Scaling Third Axis in Chart

  1. #1
    Registered User
    Join Date
    06-13-2017
    Location
    Tucson, United States
    MS-Off Ver
    2013
    Posts
    20

    Scaling Third Axis in Chart

    Hey,

    I have a chart that displays stacked bars. The X-axis is two-levels with months and names. The Y-axis is percentage. I have a secondary axis. It has dates shown as lines with markers on the same X-axis as the stacked bars.

    I'd like to draw a horizontal line across at 60%. From what I've read I should have a two points in a new series one at 0, 0.6 and one at 1, 0.6. Fomatted as a scatter plot with smooth lines. I get the line, but here comes my issue. It's a third X-axis. I need to scale that third axis so 0 is the minimum and 1 is the maximum.


    But I have no idea on how to access this third axis and scale it properly so the line extends all the way across. I can't use the axis used for the primary and secondary because they're both the two-levels month and name. Anyone know how to do this? Or a better way?

    I'm trying to do this in VBA so I can automate table + chart generation. So manually drawing a line won't work.

    Thanks!
    Last edited by Jefffey; 06-14-2017 at 12:52 PM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Scaling Third Axis in Chart

    Excel can only use primary and secondary axis systems, so I'm not sure how you are getting a "third" horizontal axis. My instinct suggests that the "dummy" series is plotted on one of the existing X axes, and that you need to figure out what values need to go into the X values for this dummy series to get it to span the desired range. My first suggestion is to change the 0 and 1 values and see what happens to the horizontal line, until you figure out what values you need here to get it to span the desired chart range.

    Beyond that, I would probably need to see your chart to better debug it.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    06-13-2017
    Location
    Tucson, United States
    MS-Off Ver
    2013
    Posts
    20
    Quote Originally Posted by MrShorty View Post
    Excel can only use primary and secondary axis systems, so I'm not sure how you are getting a "third" horizontal axis. My instinct suggests that the "dummy" series is plotted on one of the existing X axes, and that you need to figure out what values need to go into the X values for this dummy series to get it to span the desired range. My first suggestion is to change the 0 and 1 values and see what happens to the horizontal line, until you figure out what values you need here to get it to span the desired chart range.

    Beyond that, I would probably need to see your chart to better debug it.
    I played around with the values trying to get it to extend across the entire chart. But at around ~21 it just started extending the chart. I'd like the horizontal line to just go across the entire thing. I'll upload an image a little later.

  4. #4
    Registered User
    Join Date
    06-13-2017
    Location
    Tucson, United States
    MS-Off Ver
    2013
    Posts
    20

    Re: Scaling Third Axis in Chart

    Here's what the chart looks like. So the stacked bars are on the primary axis. And the line with markers are on the secondary axis. I think I put the blue line on the secondary axis too. But it'll throw me an error if I try to scale the secondary axis, obviously. I'm honestly not sure how this is working because the secondary axis is months/names. If I increase the value of the second point for the smooth line, it'll increase the length, but eventually it starts widening the chart instead of just reaching the end.

    Ideally, I'd like it to look like this but also with the days to affect change secondary data set on there.
    Last edited by Jefffey; 06-14-2017 at 12:51 PM.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Scaling Third Axis in Chart

    It is difficult to see anything in a picture. Understanding, debugging, and explaining this sort of thing needs access not only to the chart's appearance, but the underlying formatting properties of each chart element and the data in the spreadsheet. An actual Excel file would be better at helping us help you than pictures. Based on the pictures, some things I might look at:

    1) Your picture shows primary and secondary vertical axes, but only the primary horizontal axis. I find that I need to display the secondary horizontal axis so that I can access and change its properties in order to get these kinds of things to work right.
    2) Your "desired" chart only shows one horizontal line and not two. I am wondering what you are doing differently from your desired that has led to putting two horizontal line series into the chart.
    3) It appears that you are going with a "series method" for adding the horizontal line. Would it be easier to use an error bar method: http://peltiertech.com/Excel/Charts/...orzErrBar.html

  6. #6
    Registered User
    Join Date
    06-13-2017
    Location
    Tucson, United States
    MS-Off Ver
    2013
    Posts
    20

    Re: Scaling Third Axis in Chart

    Fair enough. Here's a quick example project.

    Sorry about the confusion. My ideal is of another chart that doesn't haven't that secondary axis, making the horizontal bar easy. In this chart I do want that secondary data set on top of a horizontal bar, unfortunately. Ideally, I want the horizontal line to look like that, at 60%. My problem is that every thing I've read, including the error bar method you linked me, required the secondary axis to do this. Which I'm using for something else.

    The secondary axis has the same horizontal axis as the primary axis. Different vertical axis (percentages vs. days).

    Bottom line, I want to add a horizontal line across the chart in the sheet I attached. At 60%.
    Attached Files Attached Files
    Last edited by Jefffey; 06-14-2017 at 01:43 PM.

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Scaling Third Axis in Chart

    How close does this get to what you want:

    1) Enter 0.6 into K8, copy down to K9:K42 (or select K8:K42, type 0.6 and confirm with ctrl-shift-enter).
    2) Select K8:K42, ctrl-c to copy to clipboard, select chart, ctrl-v to paste as new data series. By default Excel will paste it as a "line with markers" chart type on the secondary axis (so it is a horizontal line across the bottom of the chart).
    3) Select the new series, format series, plot on primary axis, other formatting for the series as desired.

    With those steps, I get a horizontal line at 0.6 (60%) stretching from the blank category to the left of Name1 Feb to the category to the right of Namve 7 Apr. How close is that to what you want? What is it missing or would you want different?

  8. #8
    Registered User
    Join Date
    06-13-2017
    Location
    Tucson, United States
    MS-Off Ver
    2013
    Posts
    20

    Re: Scaling Third Axis in Chart

    Awesome, thanks. Ideally, I'd like it to span the entire chart, but that's 95% there, I guess. Is there no way to get it to extend to the very borders?

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Scaling Third Axis in Chart

    I am optimistic that most things are possible with enough time, effort, and ingenuity. It's about trying some simple things to get close to what you want, then figure out what more you want, and change it to get closer to what you want.

    This is where using a scatter chart series instead of a line chart series is usually indicated. Try these steps, starting with the sample file in 6:
    1 Enter 1 into J8, 2 in J9, 0.6 in K8, 0.6 in K9
    2 select K8:K9, ctrl - c to copy to clipboard, select chart, ctrl - v to paste. Excel adds the two data points as a new line series assigned to the secondary axis system and corresponding to the first two categories (blank and Name1 Feb).
    3 Select the new series, change chart type, XY scatter. I would recommend, for now, that you use straight lines with markers, because the markers will help you see what is going on. At this point, Excel may choose to move the new series to the primary axis.
    4 Bring up the s elect d ata dialog - Select the new series - Edit - Select the X values field, and select J8 : J9 so it will use these numbers as the X values.
    A brief explanation. The horizontal axis of a scatter chart series will treat a category axis so that each category is "1". So the first category is where X=1, second category is at X = 2, and so on. With 1 and 2 as the X values, the chart should not change from the end of step 3. At this point, you should be able to enter whatever value you want in J9 and watch your horizontal line stretch to the category number you enter. If you count your categories (row 8 through row 42 is 35 categories) and enter that number ( 35 ) into J9, you should get the same line you had at the end of post 7.
    5 This is the interesting part. If you put 36 into J9, Excel extends one of the horizontal axes to fit an additional category in, which throws things off, because there are only 35 categories in the chart. If you put 0 into J8, It creates space for a category 0, which also throws things off. However, as a numeric axis, we are not limited to integers with a scatter chart series. Since the axis cross between categories, we can use .5's in J8 and J9 to extend the line out to the axes without creating the space for the non-existant categories. Put 0.5 into J8 and 35.5 into J9 and see if that is what you are looking for.

    I know that was probably a longer description than needed, but hopefully it helps you understand not only what to do, but why it works so you can more easily adapt it to other projects.

    Is that closer to what you want?

  10. #10
    Registered User
    Join Date
    06-13-2017
    Location
    Tucson, United States
    MS-Off Ver
    2013
    Posts
    20

    Re: Scaling Third Axis in Chart

    Hah! That's literally exactly what I was playing with yesterday. I didn't realize the numeric values of the x-axis corresponded to the number of the category. That's why I thought there was a third-axis. On top of that, I had no idea that you could use fractional numbers there! Exactly what I wanted to do, super appreciate it. I would've never figured out that on my own.
    Last edited by Jefffey; 06-14-2017 at 03:15 PM.

  11. #11
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Scaling Third Axis in Chart

    i used a slightly different method to MrShorty to determine the length of the horizontal line
    in J8 i put 0.5 and in J9 use formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    **even though your table finishes at row 41 i used row 42 to match your data range in your chart

    everything else i would have done the same
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Scaling individual axis on Radar chart
    By FKOC in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-10-2014, 02:40 AM
  2. HELP: Scaling of 3 axis chart
    By d247 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 01-17-2014, 11:11 AM
  3. Strange chart problem - Y axis scaling
    By shiv379 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-21-2009, 05:39 PM
  4. Chart X Axis Scaling
    By proton in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-28-2006, 12:36 PM
  5. [SOLVED] Scaling the x-axis on a line chart
    By broncogirl in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-25-2005, 09:05 PM
  6. [SOLVED] 2 Axis chart and scaling
    By Sheldon in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-22-2005, 11:05 AM
  7. [SOLVED] Pivot Chart Value Axis Scaling Question
    By farrell77 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-14-2005, 10:06 AM

Tags for this Thread

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