X-axis time problems

[dashclone]

Hello,

I'm creating a spreadsheet for anaesthesia monitoring. I have a table that calculates the time every 5 minutes (i.e. start time + 5 mins) for 3.5 hours and columns for heart rate, respiratory rate and drugs which is then plotted on a scatter graph. I've forced the major x-axis major unit to be 5 minutes.

Everything works fine for the first few values (1.JPG) then after 24 values it decides it wants to go crazy with the x-axis and display 24 hours (2.jpg) making everything unreadable. I would set the minimum manually but it obviously that will change depending on when the case starts. I can upload the file if that will help but it has some macros for other sections.

Any thoughts?

Thanks

[LJMetzger]

It looks like your graph fails when the X axis passes either 12:00 Noon or crosses over into the next day.

If you post a spreadsheet, with about 40 data points that contains X axis and the 2 Y axis values shown in 1.JPG we can probably point you in the right direction to fix your problem.

Lewis

[dashclone]

Hi Lewis,

Thank you for the reply. It doesn't seem to matter if it crosses past noon, I've tried with starting at 11am and it runs over midday happily as shown here 3.JPG from this data 4.JPG.

I've attached a macro-free (and hopefully VB free) version of my spreadsheet as well for people to play with and see if they can replicate my problem.

Thanks

Rob

[LJMetzger]

My results from your test file give the same GOOD results you see in 3.JPG. My speculation is that there is some data in the X axis somewhere that causes you a problem.

I suggest the following to try to narrow down the problem.
a. Plot all X values with only HR as the Y axis.
If you get bad results, shorten the X axis values until you get good results.
This may show you where a bad data point lies.

b. If a. has no problems, add the second Y axis.
Repeat as in a. above.

Good luck.

If you're still having problems post another file containing the minimum amount of data
that causes a problem. You may want to include VBA that generates the graph only.
Delete all extraneous sheets if possible.

Lewis

[dashclone]

Hi Lewis,

I've done as your suggested - the bad point lies at A44. Even just plotting time vs HR or time vs RR, every time I head past A43 then the x-axis resets the time to 00:00. I've checked my formulae and there isn't anything different from one cell to the next, so I really don't know why there is a problem with that one cell. Does excel have a limit to length or something? There isn't any VB that refreshes the graph, only the table when filters are applied, and the problem repeats with this disabled.

Thanks

Rob

[LJMetzger]

Unfortunately, I was able to duplicate your results exactly at A44 on your spreadsheet, and on another independent spreadsheet using Excel 2003 on an XY Scatter graph.

However, I did have success on a Line Graph with markers. The X Axis behaved itself.
You might want to try using a "Line Graph with markers". Keep everything else the same.

It seems like the time '13:10' causes the problem on the XY Scatter graph. When I changed all the times to 4 hours earlier, everything worked fine.

Lewis