Loss of precision in chart

[kristym]

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!

 

[mole999]

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]

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]

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 ?

 

[kristym]

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]

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]

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.