+ Reply to Thread
Results 1 to 8 of 8

Secondary scatter error bars and their Y-axis points not behaving as expected...

  1. #1
    Registered User
    Join Date
    03-16-2020
    Location
    Bulgaria
    MS-Off Ver
    365
    Posts
    7

    Secondary scatter error bars and their Y-axis points not behaving as expected...

    Hi there,

    I am trying to basically force Excel to be used as a Project Timeline.

    I have had success so far with what I have needed adding Project statuses, Start, end date, % completed to be plotted as error bar etc.

    However I am currently trying to add basically :

    A secondary axis (with a scatter (points based on the planned start) and duration based on delta between Planned Start and End.
    This duration I want to plot on each row as error axis (plus).

    I am facing issues arranging them on the Vertical axis. I've tried creating the values for the axis manually.
    As long as the rows are under 10 it works fine.
    0.2, 1.2, 2.2 etc.
    But when I reach 10.2 and above it goes all over the place.

    Any ideas?

    works.png
    not_working.png

    Attached is my file.

    *one macro is used only to set the beginning/end of chart plotted easier instead of digging in.
    Attached Files Attached Files
    Last edited by wbgenetic; 05-08-2020 at 07:24 AM. Reason: added photos

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Secondary scatter error bars and their Y-axis points not behaving as expected...

    You need to force the secondary axis min/max when the number of rows changes.

    Please Login or Register  to view this content.
    To illustrate I have added code the adjust the axis when the dates change, but you need to do when the data rows change
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    03-16-2020
    Location
    Bulgaria
    MS-Off Ver
    365
    Posts
    7

    Re: Secondary scatter error bars and their Y-axis points not behaving as expected...

    That's actually something that crossed my mind but I didn't check for some reason lol

    So I've altered to code so it sets the minimum to 0 (instead of -2).
    I also added a formula in a cell to count the rows and referenced the cell in the vba code.
    This seems to WORK (most of the time...).

    How do I limit this to row inserted only?
    It also does not work properly when I have a formula in the reference cell.
    I tried this with the dates (adding max/min formulas where I set chart start/end) but it does not trigger the VBA as it does not see it as a change.
    Maybe a refresh button of some sort that simply triggers the changes ?


    Additionally I need to make it constantly populate the Y-axis values in column P as it does not fill them properly when I add/remove rows ...
    It basically needs to be 0.2 for the lowest row and then increase by 1 for each row ... I tried making it into a table, flash fill etc ... it remembers an old formula that I used to use there ...
    Last edited by wbgenetic; 05-08-2020 at 09:21 AM.

  4. #4
    Registered User
    Join Date
    03-16-2020
    Location
    Bulgaria
    MS-Off Ver
    365
    Posts
    7

    Re: Secondary scatter error bars and their Y-axis points not behaving as expected...

    So I created a refresh button which runs through the exact same VBA.
    Then I added a formula for the rows: =(ROW()-ROW($P$15))*-1-0.8
    This avoids the problem with the deleted rows/reference errors etc.

    Maybe If I can force this to work with inserted/deleted rows instead of the refresh button that would be great.
    Attached Files Attached Files

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Secondary scatter error bars and their Y-axis points not behaving as expected...

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    03-16-2020
    Location
    Bulgaria
    MS-Off Ver
    365
    Posts
    7

    Re: Secondary scatter error bars and their Y-axis points not behaving as expected...

    This does not do anything to me ... I mean when I insert a row nothing changes ...
    Anyways I mapped this to a button - I might move this as a new question to the VBA section as I am unable to make it work and it would be great.

    What I struggle with now is something extremely stupid. I am trying to place the date between the tick marks.
    This is normally available as an option for the Vertical axis labels, however I have the option I speak of only for the Horizontal labels for some reason ... Any ideas what I might have broken?

    Cheers

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Secondary scatter error bars and their Y-axis points not behaving as expected...

    You need to insert a row and then enter some data. The axis will not update until you change a value in column F.

    You would need to check which series are on what axis and what chart type those series are. The option is only available for category axis, which can be horizontal or vertical depending on chart type.

  8. #8
    Registered User
    Join Date
    03-16-2020
    Location
    Bulgaria
    MS-Off Ver
    365
    Posts
    7

    Re: Secondary scatter error bars and their Y-axis points not behaving as expected...

    Quote Originally Posted by Andy Pope View Post
    You need to insert a row and then enter some data. The axis will not update until you change a value in column F.

    You would need to check which series are on what axis and what chart type those series are. The option is only available for category axis, which can be horizontal or vertical depending on chart type.
    Yes I saw what you did there. Thanks for the suggestions.
    I am actually thinking of making this work for the Chart start and Chart end as well so it becomes automatic.

    So basically if a change is made to Column E or Column F it auto adjusts based on the values in cell D2 / D3.
    And in D2 I have min (Actual start) -30days (so it aligns the days properly).
    in D3 I have Max (actual end) + 30 again so it matches it right.



    As for the categories/series - I do not think that I will be able to match those as I wish. So i just play with the custom formatting and this should suffice.

+ 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. Secondary Axis without overlapping bars
    By treva26 in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 03-18-2021, 02:58 PM
  2. Non-overlapping bars when using some series on secondary axis
    By vikas.trades25 in forum Excel General
    Replies: 1
    Last Post: 05-10-2019, 05:26 AM
  3. [SOLVED] Bar Graph With 2 Axis - Bars On The Primary Axis Overlap Bars On The Secondary Axis
    By Variablez in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 03-11-2013, 08:38 AM
  4. Bars Stack when Selecting Secondary Axis
    By jbeardslee in forum Excel General
    Replies: 1
    Last Post: 10-16-2012, 04:07 PM
  5. Replies: 2
    Last Post: 08-15-2012, 11:56 AM
  6. Replies: 1
    Last Post: 05-03-2006, 09:20 PM
  7. Two bars for a Chart with a Secondary Axis
    By GregBausman in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-01-2005, 07:06 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