Trendline equation incorrect (Numbers)

Question

Level 1

10 points

Trendline equation incorrect (Numbers)

I made a scatter plot in Numbers and drew a linear trendline which by eye is a good fit to the data. Then I displayed the associated equation, but the equation it displays does not correspond to the trendline. To prove this to myself, I made a new column with the equation and plotted it, and it's very clearly wrong. I also copied the data into Google Sheets, repeated the process, and plotted the trendline Sheets gives me which appears correct within precision. I've included screenshots below.

Does anyone have any ideas for how this might be something I'm doing incorrectly? Or is this just a bug in Numbers?

Posted on Apr 4, 2019 8:43 AM

Reply

Answer 1

Top-ranking reply

sssim Author

Level 1

10 points

Apr 4, 2019 12:27 PM in response to sssim

I found a mistake that I made which solved the problem, though in a way I find unsatisfying. Instead of choosing "2D Scatter" as I had intended, the plot was "2D Line" type. I would expect a "2D Line" type to be able to do this, but apparently it can't. Changing the plot type to "2D Scatter" gave me the correct equation for the trendline.

S.M.

Reply

Answer 2

jaxjason

Level 5

5,063 points

Apr 4, 2019 12:14 PM in response to sssim

I am fairly sure your problem is with the zeros. Did you have a reading of 0 for everything from 50 - 120?

If you take those out, you should find the equation becomes what you would expect. Delete any row that you dont have a reading for. Leaving it in implies you took a good reading and that is messing you up.

Jason

Reply

Answer 3

Badunit

Level 7

30,390 points

Apr 4, 2019 2:18 PM in response to sssim

All the charts other than scatter are category charts. On a category chart, the X axis is text labels, all evenly spaced. They might as well be letters or words. The trendline on that line chart doesn't care what your X axis "values" are, it uses 1,2,3, etc. . If you replace your X values in your table with 1,2,3 etc, the line works out perfectly.

Reply

Answer 4

sssim Author

Level 1

10 points

Apr 4, 2019 12:21 PM in response to Wayne Contello

Wayne,

Thanks for your response. This is definitely not a significant digit problem, and the issue is not that I can't get the right answer, it's that I can't get the right answer when using Numbers. As mentioned in the post, I was already able to get the solution by copying the data into Google Sheets.

S.M.

Reply

Answer 5

sssim Author

Level 1

10 points

Apr 4, 2019 12:24 PM in response to jaxjason

Jason,

Thanks for your response. At the time of the screenshot 50-120 was filled with "--" which was not interpreted as "0" by Numbers, so removing these didn't solve the problem. Equivalently, restricting the data being filled into the table to Rows 2-8 didn't solve the problem.

S.M.

Reply

Answer 6

sssim Author

Level 1

10 points

Apr 4, 2019 12:42 PM in response to Badunit

Badunit,

Correct. Clearly there is something I don't understand about what a "category chart with lines" means... As you show, these two plots look identical, so what's the functional difference? What is the equation on the line chart supposed to represent?

S.M.

Reply

Answer 7

Apr 4, 2019 9:29 AM in response to sssim

My experience is that the equation is correct except for the part that it does not include enough significant digits. My solution is to use a site like this to get a regression:

https://arachnoid.com/polysolve/

There are others and I am not endorsing any particular one. the one I posted is an example only

Reply

Answer 8

Badunit

Level 7

30,390 points

Apr 4, 2019 12:39 PM in response to sssim

You did not create a scatter chart, you created a category chart with lines. Which, now that I have posted my response, I see you figured out on your own.

Reply

Trendline equation incorrect (Numbers)

Similar questions