Loss of precision in chart

kristym

New Member
Joined
Nov 30, 2015
Messages
4
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!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,286
Office Version
2019, 2016, 2013
Platform
Windows
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
 

kristym

New Member
Joined
Nov 30, 2015
Messages
4
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?
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,286
Office Version
2019, 2016, 2013
Platform
Windows
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:

kristym

New Member
Joined
Nov 30, 2015
Messages
4
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.
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,286
Office Version
2019, 2016, 2013
Platform
Windows
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)
 

kristym

New Member
Joined
Nov 30, 2015
Messages
4
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,544
Messages
5,445,099
Members
405,316
Latest member
joaoamaro

This Week's Hot Topics

Top