Numbers Graph: How to plot a variable by Day of Year?

Question

Level 1

71 points

Numbers Graph: How to plot a variable by Day of Year?

Have a wish list for doing a graph.

Disclaimer: Don’t use Numbers often enough and wind up forgetting how to do things; hence, the need to request assistance. Searched somewhat briefly through many days and used a few search terms to no avail. Numerous other requests for converting dates to day of year though but figured a work-around without having to write a formula.

Want to track bike riding distances over time. Want the data points to line up with the Day of Year (DoY in government-speak if one prefers abbreviations).

Couldn’t figure out how to have the mile data points line up with the DoY tick marks. Seems that should be easy but ….

(I’m sure others have experienced this challenge but didn’t find any solutions. Searched in all the wrong places’ I guess)

Any support would be appreciated!

Earlier Mac models

Posted on May 22, 2023 7:19 PM

Reply

Answer 1

Top-ranking reply

Yellowbox

Level 7

21,197 points

May 22, 2023 7:41 PM in response to Searchin99

Hi searchin99,

The function DATEDIF will calculate the number of days for any date from the start of the year.

Formula in B2: DATEDIF(A$2,A2,"D")+1

Fill down.

Select columns B and C. Insert a Scatter Plot and change the Min and Max of each axis to suit.

Reply

Answer 2

Searchin99 Author

Level 1

71 points

May 22, 2023 9:58 PM in response to Yellowbox

Hi Yellowbox,

Haven't tried it yet but it obviously looks like it will work. Also did a search for DATEDIF and found a Community reply about it from 2010 (!), First hit.

I'm actually looking at several years of data with a different syntax from day/month/year. Currently the dates are in two columns with 'month' (jan, feb, mar, apr,....), then 'day', with the two items under a column heading of 'year'; HOWEVER, the 'year' can be easily added as a column.

Its not a deal breaker (I'm really pleased with this solution), .... but just wondering. If it isn't easy, I'll just re-type everything (no problem).

This DATEDIF is a very good thing to know about and in the future one can set up the spreadsheet properly ahead of time.

Reply

Answer 3

Badunit

Level 7

28,300 points

May 22, 2023 9:58 PM in response to Searchin99

The key is to use a scatter chart, as Yellowbox mentioned at the end of his post. The icon looks like a bunch of + signs. It is the only true X-Y chart. You can format it to be a line chart, not just points. The other line charts are "category" charts and will space the X axis data evenly along the axis. For them, the X axis is just text (categories).

To make a scatter chart, the X axis data should be in a regular data column, not a header column. It doesn't have to be but you'll be fighting it and adding additional steps to the process if it isn't.

Reply

Answer 4

Searchin99 Author

Level 1

71 points

May 22, 2023 10:10 PM in response to Searchin99

Super!

Very nice to know... the difference between scatter charts and the others.

"The devil is in the details".

Haven't used scatter charts before and didn't know that they were actually capable of other things (useful in my case).

Thanks for taking the time to add to the discussion!!!

Really appreciate it.

Very Helpful!

Reply