Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Loss of precision in chart

  1. #1
    Board Member
    Join Date
    Nov 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Loss of precision in chart

    Hi

    I'm creating a Gantt Chart in Excel that shows time elapsed between start times and end times. I want to have my y-axis to have units of 30 seconds, so I've got the major axis units set to 0.0003472222222222 (equal to 1/24/120).

    This works fine if my graph doesn't span more than two hours, my labels work perfectly and display as 14:00:00, 14:00:30, 14:01:00, ...; however, when the graph spans more than a few hours, I end up with intervals of just over thirty seconds, e.g. 15:00:00, 15:00:30, 15:01:00, 15:01:30, 15:02:00, 15:02:31, ....

    Is there any way to work around this? It seems odd since it's fine until I try and show more than four hours on the graph. I've tried adding more significant figures to my major axis units, but to no avail.

    Thanks!

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,608
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Loss of precision in chart

    Excel has a 15 decimal place limit, I guess the way you are constructed you are seeing a rounding issue, there must be an alternate way to configure the axis
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    Board Member
    Join Date
    Nov 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loss of precision in chart

    Thanks for your reply.

    Can you explain what you mean by 'the way you are constructed'? Also by an alternate way to configure the axis? I'm currently doing it by selecting Format Axis, and then setting the units in the text boxes. Are you suggesting that there's a different way to go about doing this?

  4. #4
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,608
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Loss of precision in chart

    As a test I created 01:00:00 and 01:00:30 in A1&A2, then dragged down to beyond 6a.m., in B1 1, B2, 2, double click to fill down

    selected the data and had it create a simple line chart, that displayed over 5.5 hours, in visible 4 minute steps, axis type is automatic, number I set as Time (this is displayed on the X axis)

    I tried to force the data to Y and told that it is limited to 255

    I transposed the data, and it still modelled on the x axis

    I can't replicate your problem, beyond not being able to get it on the Y axis.

    I cannot also find a Gantt Chart as part of excel, a number of third party providers, maybe its a limit of the template ?
    Last edited by mole999; Dec 1st, 2015 at 09:12 AM.
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  5. #5
    Board Member
    Join Date
    Nov 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loss of precision in chart

    Thanks again for your reply.

    My Gantt Chart isn't a template - I've used the tutorial here to create it in a bit of a hacky way. How to Make a Gantt Chart for Repeated Tasks | Katherine S. Rowell

    I've also got my number set to time.

    The thing that's really baffling me is the fact that it works completely fine if my graph is displaying fewer than three hours - it's only when it's graphing a period greater than about three hours that I lose the accuracy of the units.

  6. #6
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,608
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Loss of precision in chart

    Sorry, I haven't got an answer, I pushed a copy of that file to eight hours, and like you the time stamps don't work. I set 30 seconds to 0.000347222222222. When I then add the same amount each time together as time I got to over 64 hours, with no deviation (as purely calculations on the sheet)
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  7. #7
    Board Member
    Join Date
    Nov 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loss of precision in chart

    No worries. Thank you very much for looking at it!

    If I do find an explanation or a solution I'll make sure to let you know.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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