How to calculate polynomial trendline x value when using a date?

Question

Level 1

4 points

How to calculate polynomial trendline x value when using a date?

So I have a chart that I have created in Numbers, with a trendline using a polynomial function, and I've got it to display the formula, but I cannot for the life of me, figure out what `x` value I'm supposed to use to get the resulting `y` value!

`x` is a date / time, so clearly it must be something related to the date value. But I've tried using ms since the unix epoch, secs since the unix epoch, etc. each to no avail. Some "other" spreadsheet tools make it clear how they convert dates into numbers when creating polynomial trendlines, Numbers however, does not.

Does anyone know what value I am supposed to use!?

For those that might not be able to read the graph easily, an `x` value of 01/01/2019 00:00:00 should give a `y` value of 1,000~.

MacBook Pro TouchBar and Touch ID, macOS High Sierra (10.13.4), null

Posted on Apr 13, 2018 1:40 AM

Reply

Answer 1

Top-ranking reply

Yellowbox

Level 7

21,331 points

Apr 13, 2018 4:16 AM in response to Siyfion

Hi Siyfion,

Numbers stores Date & Time values as serial numbers, but I don't know how to see what goes on "behind the curtain".

The only solution I can think of is to convert dates to number of days from the start. Using your data, I selected columns B and C and inserted a 2D Scatter

Are you using a 2D Scatter or a 2D Line?

Regards,

Ian.

Reply

Answer 2

Yellowbox

Level 7

21,331 points

Apr 13, 2018 3:02 AM in response to Siyfion

Hi Siyfion,

Please post a screen shot of the data table.

What date format are you using? It looks like dd/mm/yyyy with a default time as midnight.

Just to kick off the discussion, I had a play with converting each date to a Duration (days) from the starting date:

Or you might try DUR2DAYS to convert a Duration to a Number and use that column as the X values.

More information will lead to a solution 😉.

Regards,

Ian.

Reply

Answer 3

Siyfion Author

Level 1

4 points

Apr 13, 2018 3:05 AM in response to Yellowbox

Hi Ian,

Thanks for your prompt reply, the data I'm using to generate the chart is really simple:

Then I'm using the Trendline - Polynomial option to create the line, with the "Show Equation" checkbox ticked.

What I really need to be able to do, is be able to get a Y value for a given date. The equation should give me this, but the problem is, X is a date, so I have no idea how to convert the date into a numeric that can be used in the equation! It's like I need to know how Numbers is converting it, to know how I need too...?

Reply

Answer 4

Siyfion Author

Level 1

4 points

Apr 13, 2018 4:17 AM in response to Yellowbox

2D Scatter and you're perfectly correct of course, if I convert the dates to a 0-based "days" then while it makes the graph slightly less easy to read (from an X-axis pov), it makes it MUCH easier to calculate. Thanks!

Reply

Answer 5

Yellowbox

Level 7

21,331 points

Apr 13, 2018 4:31 AM in response to Siyfion

Hi Siyfion,

Glad it worked, and thanks for the gold star, the 'greenie' and your feedback.

Regards,

Ian.

Reply

How to calculate polynomial trendline x value when using a date?

Similar questions