Graphs using dates and spacing the dates proportionately

Question

Level 1

8 points

Graphs using dates and spacing the dates proportionately

I have the same question as user Bwalt did in this thread: Graphs using dates - Apple Community. Quoting Bwalt's original question:

I have a column of values, each associated with a particular date in the adjacent column. The dates and values are listed in chronological order, but they are not evenly spaced in time. (Values are recorded on randomly spaced dates.) I want to plot values as a function of time with the dates spaced according to the number of elapsed days. A standard approach yields all dates equally spaced with no regard for the number of elapsed days between the actual dates.

I understand the answer by user pjl123:

I suggest you insert an additional column. you can write a formula to determine the number of days from the last event. use that number of days instead of the actual date as the x axis which would make the values follow that spacing.

but I'm having trouble making it work. I can make a column that has the number of days since the actual date, but when I try to make a graph using that column as the x-axis, it has the same problem - the entries are equally spaced on the x-axis. Here's a screen-shot of my spread-sheet, including the graph:

The graph is a 2D Area graph. Can anyone suggest what I might be doing wrong?

Thanks very much for any help.

Posted on Feb 19, 2020 12:41 PM

Reply

Answer 1

SGIII

Level 8

37,556 points

Feb 19, 2020 11:03 PM in response to PlunderBunny

I see your dates are right-justified so I am guessing you are in a region using dd-mm-yyyy format and all is well with that.

But the shading in your table suggests you have the dates in a Header Column. Numbers thinks you want to do a "category" type chart where each category is evenly spaced. If you want to plot unevenly spaced data over time you need to use a "scatter" type chart.

So you need to convert that Header Column to a regular column (after clicking anywhere in the table go to Table in the menu choose Header Columns and 0). Then insert a 2D-Scatter chart. This will give you unevenly spaced "entries" (data points). Note, though, that the axis labels are always evenly spaced. Here is a start at what you can do. You can experiment to make this prettier.

SG

Reply

Answer 2

Feb 19, 2020 2:44 PM in response to PlunderBunny

a couple format comments:

1) omit units as part of the entry in each column. Instead label the column and indicate units. e.g. change "Volume Full" to "Volume Full (L)". then leave the "L" off each entry

2) same comment for Measured and Actual Level

3) the date in column A is an unusual format (at least in the US). Make sure the date is actually detected as a date by lookin in the cell format menu:

column A is NOT formatted as a proper date for my location. They are justified (automatically) to the left:

formatting them to a proper format results in them being automatically right justified:

select the cell format from the pop-up menu:

Reply

Answer 3

PlunderBunny Author

Level 1

8 points

Feb 20, 2020 11:45 AM in response to SGIII

Thank you SGIII for the detailed reply. As you have surmised, the critical part I was missing was understanding that I needed a scatter type graph, and once I followed your steps, the graph came out the way I wanted it to.

Reply

Graphs using dates and spacing the dates proportionately

Similar questions