+ Reply to Thread
Results 1 to 7 of 7

Secondary Axis tick mark formatting problems

  1. #1
    Registered User
    Join Date
    06-25-2015
    Location
    USA, Utah
    MS-Off Ver
    Microsoft Office Professional Plus 2013
    Posts
    4

    Secondary Axis tick mark formatting problems

    I have a scatter with smooth lines chart in excel 2013. The values on the primary y-axis range from -4 to 4 (excel automatically created this range). I have one series graphed relative to this primary y-axis. I have graphed another series relative to a secondary y-axis. This secondary y-axis must be related to my primary axis by the following ratio: 1 unit (in this case 1 inch) on the primary axis must equal 0.514475 units (in this case percent) on the secondary axis. Because of this ratio relationship I had to manually change the maximum and minimum bounds on the secondary axis to 2.0579% and -2.0579% respectively. My problem is two fold. One, the tick marks are not anchored about zero. Two, if I change my "Major Units" to force the tick marks to land exactly on zero, then the rest of the tick marks are not clean increments (example: -1.5, -1.0, -0.5, 0, 0.5, 1, 1.5). Is there a way that I can anchor the tick mars about zero on the secondary axis, increment these tick marks in clean number increments (like the aforementioned example), and still have the secondary axis be related with a ratio to the primary axis? Any help would be very welcome as I am completely stumped on a fix. A VBA fix is not preferable as I am not proficient in VBA. Thanks.

    Here is a picture of my current chart. Note that the blue line actually has an orangish line underneath it. One series corresponds to the primary axis and the other series corresponds to the secondary axis, and both series currently occupy the exact same space on the graph because I have the axis related by the ratio I have already mentioned.

    Capture.PNG

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

    Re: Secondary Axis tick mark formatting problems

    This may be of interest: http://peltiertech.com/secondary-axe...tional-scales/ Note that he suggests two possible approaches. In the first approach "standard secondary axis construction", he simply fixes the axis limits for each axis in a way that preserves the proportional scaling of the two axes. I have on occasion also automated this approach using a calculate event macro.

    The second approach creates the secondary axis as a dummy series that labels the dummy series as if it were an axis.
    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-25-2015
    Location
    USA, Utah
    MS-Off Ver
    Microsoft Office Professional Plus 2013
    Posts
    4

    Re: Secondary Axis tick mark formatting problems

    I have extensively looked at the link you posted. The first approach does work somewhat; however, my problem is that the -4 on my chart does not correspond with a clean value on the secondary axis like his does. The ratio that my chart uses is messy and does not work nicely like his does.

    I have not yet tried the second approach because I am on a work computer and am unable to download extensions, programs, etc. I would just think that Microsoft would have a simpler way to do this. Maybe they do not. Thanks for the help though.

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

    Re: Secondary Axis tick mark formatting problems

    No, MS does not (at least not in Excel). I know that other graphing packages have a "format secondary axis scale to be a (linear) function of the primary scale" option. You may look at other graphing packages to see which ones would support the secondary axis scaling formula that you want.

  5. #5
    Registered User
    Join Date
    06-25-2015
    Location
    USA, Utah
    MS-Off Ver
    Microsoft Office Professional Plus 2013
    Posts
    4

    Re: Secondary Axis tick mark formatting problems

    I am not familiar with other graphing packages. Do you have a link or an explanation of where I can take a look at those?

    Thanks

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

    Re: Secondary Axis tick mark formatting problems

    I briefly tried DPlot (http://www.dplot.com/index.htm ), but could not justify the expense.
    Many of my colleagues like Grapher: http://www.goldensoftware.com/products/grapher
    I don't know what hoops you would have to jump through to get either of these graphing packages.
    Search engines suggest a few "open source" graphing packages, but I have not tried any of them, and it may be more difficult for you to get an open source application installed (depending on your IT rules).

    If getting permission to install an additional program is the only thing stopping you from doing Jon's second approach, you can do it without Rob Bovey's add-in. You just need to create and format each data label for each point of the "dummy" axis individually. Rob's add-in is usually recommended because it makes the task so much simpler.

  7. #7
    Registered User
    Join Date
    06-25-2015
    Location
    USA, Utah
    MS-Off Ver
    Microsoft Office Professional Plus 2013
    Posts
    4

    Re: Secondary Axis tick mark formatting problems

    Thanks for your help. I appreciate it.

+ 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. Keep chart data on tick marks when using secondary axis
    By jscott15 in forum Excel General
    Replies: 5
    Last Post: 04-18-2023, 01:58 AM
  2. How to get vertical axis to show a tick mark
    By amartino44 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 02-05-2013, 08:42 PM
  3. Formatting date tick marks on h-axis
    By Lurendrejer in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 11-08-2010, 07:47 AM
  4. How to change tick mark labels in z axis from values to texts?
    By Flroa in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-11-2006, 01:35 PM
  5. [SOLVED] Can tick mark labels be flipped about axis?
    By [email protected] in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-20-2005, 12:05 PM

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