Wrong axis values in Numbers chart

Hi SBT,

It looks like a Scatter Plot:

If so, it is behaving as it should. It is scaling the X axis as linear, and not showing exact data values as steps.

Or is it a Category Line Chart:

which treats each X value as a distinct step (but is not linear).

A Scatter Plot requires both X and Y values to be in Body (non Header) columns.

A Category Chart requires X values to be in a Header Column and Y values to be in a Body Column.

Both types of Chart work best when the axis names (leave home and commute time) are in a Header Row.

The power of Numbers is that it allows several small, discrete tables on the "canvas" of each sheet. Each table has its own function (perhaps to act as the source data for a chart). The data can be pulled from an input table and then formatted to suit the purpose (perhaps with Header Rows and Columns).

Regards,

Ian.

SBT,

The explanation is simple, but the fix is more involved.

Numbers has no value type for Time of Day independent of day. It only has Date&Time, the time of day on a particular date. So, a Time entered tomorrow is 24 hours later/greater than the same time entered today. The Date part of a time is always assumed to be the date when the value is entered, unless the full date and time is entered. You can imagine how long your x-axis could become!

Numbers has a function that strips the date from a date&time value, called TIMEVALUE. The result of TIMEVALUE is a decimal portion of a day representing a particular time of day.

You seem to be very capable at spreadsheet work, so I imagine you can take it from here, but we'd be happy to help if you need it.

Jerry

Here's a solution that requires very little in the way of setup changes, but some minor data entry changes.

The "Times" are all actually Durations, and must be entered using the format 7h30m.

The format will automatically change to 7:30, if that is the way you have the cells formatted: hh:mm.

Doing is this way, the math is simple and you don't have to worry about the Dates.

Jerry

Instead of using TIMEVALUE(B) as the X axis, use TIMEVALUE(B)+TODAY(). Set the X axis to Date & Time but with only the time showing

SB,

As Badunit notes, a TODAY() plus a fraction of a day equates to the time of day on the current day, which is good enough if you aren't displaying the actual date, and it's exactly what you are asking for. You could also plot against Hours, which is can be calculated by: =TIMEVALUE(date/time)*24.

Most people can relate to 24-hour time.

Jerry

"it's that that the scale depicts impossible leave times - they extend beyond the range of the data."

Actually, they don't.

The "range of the (x-axis) data" is from February 4, 2014 at 6:45 AM to February 14, 2014 at 6:52 AM, a range of 240 hours and 7 minutes. All data points shown are plotted within that range.

Using either or Badunit's or Jerry's suggestions removes the date difference from the plotted x values. You can also do that by re-entering all of the start and arrival times, and doing all of the new entries on the same calendar day.

Date and Time values (which includes all of the entered values in the bodies of columns A, B and C) always include both a date part and a time part.

If only the date part is entered (as in column A), the time part is set to 00:00:00 (midnight at the beginning of that day and only the date part is displayed.

If only the time part is entered (as in columns B and C), the date part is set to the date on which the entry was made, and only the time part is displayed.

Entering the time parts all on the same calendar day removes the differences in date from the calculations, and from the range to be displayed on the chart.

Regards,

Barry