Changing X axis scale outwards from middle of graph via line

[FHstudent]

I have a set of 24 hour data for the x-axis, and then corresponding data values. Within the 24 hours, an event starts, and I've manually set it that time point has become time "0:00", and then all the time before is now "-" relative, and time above is "+" relative, at a frequency of 2 seconds. Ex.
Column A Column B Column C
23:48:11 -0:10 19.924545
23:48:13 -0:08 19.924545
23:48:15 -0:06 19.924545
23:48:17 -0:04 19.866797
23:48:19 -0:02 19.837347
23:48:21 0:00 19.837347
23:48:23 0:02 19.793749
23:48:25 0:04 19.736093
23:48:27 0:06 19.692526
23:48:29 0:08 19.750151
The equation I used was: After event (to get points 0 and above): (B1-$A$1+(B1<$A$1)) Before event: TEXT(ABS(B1-$A$1),"-[m]:ss") Where B1 = value you're subtracting, A1 = event start
A line graph works, and displays the x axis as shown in the middle column, but when I try to do a XY plot, it shows the same trend, but not the correct x-axis values (i.e. shows 1000, 2000, 3000, etc.). I also tried using value() on the values before since I had to use text, but that also doesn't work since it just turns everything to 0, so a line plot seems to be the only for me to display the x-axis as shown in the column.

Is there a way to change the x-axis scale so that it shows the time every 5 mins but going OUTWARDS from 0 instead of from the very left.

i.e. z6Pqswb.png


So I want the x-axis to make increments outwards from the zero point at nicer time intervals (ex. every 5 mins, instead of every 3min 51s)

If the only way is to convert the chart type to a XY plot, how can I do this while keeping the x-axis still displaying ex. -0:02, 0:00, 0:02, etc. instead of 1000, 2000, etc.
Thanks

[cbatrody]

Hi,

Did you try setting the X-Axis Major Unit to 5 minutes i.e (0.003472)?

[FHstudent]

Hi,
I did, but it starts from the very first number instead of in the middle going outwards, so it doesn't show 0:00, but whatever is next (ex. 0:10). It also only works with XY scatter plots, which I can't seem to be able to successfully convert over.

Hi,

Did you try setting the X-Axis Major Unit to 5 minutes i.e (0.003472)?

[MrShorty]

The challenge you have -- especially when it comes to create a scatter chart -- is that Excel's default calendar cannot display negative time. This is why you have to create a TEXT() string in the spreadsheet for the negative times, and why you cannot use the same data on a scatter plot.

One possible solution is to use the optional 1904 date system (under Excel options). Under the 1904 system, Excel can handle negative times, and you should find, for this specific problem, that you don't need special formulas or other considerations to handle the negative times. The main caution in using the 1904 system is that it can create other problems. As a global setting, it will change the calendaring in all of your spreadsheets. So, in fixing this problem, you might create other, unintended problems in other spreadsheets that depend on the 1901 date system. If this spreadsheet needs to be shared, then the other users need to understand that this spreadsheet uses the 1904 system and be prepared to handle the consequences of that switch in their spreadsheets. Used with appropriate understanding and discretion, this might be a good approach.

Other solutions that come to mind depend on how important it is to you to have the values display as time (m:ss). If you can allow it, delete the time format so the values are the underlying date/time serial number (as a fraction of a day). If you can work directly with the serial number, then you can work with negative values and get your scatter plot to do what you want. Or convert all of your numbers to decimal minutes and completely abandon Excel's built in default calendar (which means abandoning the mm:ss display format).

As I see it, one way or another you need to get away from using the default 1901 date system to get what you want. I'm not sure what will be the best approach for you.

[FHstudent]

Hi, thanks for replying.
I've tried using the 1904 system, and it seems to work, but for some reason I can't get it to plot the negative times. The times show up properly as negative values in the cell, but the negative time values won't show on the x-axis. It remains blank, while everything above 0 would show, so half the x-axis is empty. Do you know how I would be able to get the negative values to show on the plot?

Thanks

The challenge you have -- especially when it comes to create a scatter chart -- is that Excel's default calendar cannot display negative time. This is why you have to create a TEXT() string in the spreadsheet for the negative times, and why you cannot use the same data on a scatter plot.

One possible solution is to use the optional 1904 date system (under Excel options). Under the 1904 system, Excel can handle negative times, and you should find, for this specific problem, that you don't need special formulas or other considerations to handle the negative times. The main caution in using the 1904 system is that it can create other problems. As a global setting, it will change the calendaring in all of your spreadsheets. So, in fixing this problem, you might create other, unintended problems in other spreadsheets that depend on the 1901 date system. If this spreadsheet needs to be shared, then the other users need to understand that this spreadsheet uses the 1904 system and be prepared to handle the consequences of that switch in their spreadsheets. Used with appropriate understanding and discretion, this might be a good approach.

Other solutions that come to mind depend on how important it is to you to have the values display as time (m:ss). If you can allow it, delete the time format so the values are the underlying date/time serial number (as a fraction of a day). If you can work directly with the serial number, then you can work with negative values and get your scatter plot to do what you want. Or convert all of your numbers to decimal minutes and completely abandon Excel's built in default calendar (which means abandoning the mm:ss display format).

As I see it, one way or another you need to get away from using the default 1901 date system to get what you want. I'm not sure what will be the best approach for you.

[Andy Pope]

In order to get 0 at the axis you will need a Min value and Major Unit that are divisible.

[MrShorty]

Unfortunately, I don't really know what to suggest. I rarely work with Excel's date/time system -- preferring to simply enter times as decimal minutes or decimal seconds and treat them accordingly.

Out of curiosity, I tried this in Excel XP/2002. It seemed to work just fine in 2002, but would not display the axis correctly in 2007. Is this functionality that was lost in the "upgrade" to 2007? Perhaps one solution to this variation of the problem is to downgrade to an earlier version of Excel.