0

I'm using Microsoft Office Excel 2007 for some statistics work for school. I made a linear trendline on a graph corresponding to a data set. This is what it looks like:

Graph

The data set in question is 4pcmm. All looks well.

However, the equation of the trendline is off. For example, if we calculate it at an X position of 50:

y = (4538.1 * 50) + (1 * 10^6)

Punch this into Excel (or just a calculator) and we get 1,226,905. However the graph clearly shows that at 50, the line is above 1,600,000. Hell, the line never even goes below 1,400,000.

So what's going on? Have I screwed something up or is it Excel which is at fault? And how do I fix this?

| improve this question | | | | |
  • 1
    I think it has to do with the constant you are adding. If you look at the graph, the constant should be 1.45e+06. If you want to check, use SLOPE() and INTERCEPT() on the data. – LDC3 Nov 5 '14 at 4:54
  • This is the second recent question I've seen with Excel reporting incorrect trendline parameters. Up until then, I had never seen a case. I wonder if something like a recent Microsoft update introduced some type of corruption. – fixer1234 Nov 5 '14 at 5:07
  • @LDC3: I can't really use SLOPE() or INTERCEPT() because I don't have a data set of the points of the line itself. The trendline was made using "Add Trendline" on the 4pcmm data set. – puggsoy Nov 5 '14 at 6:16
  • @fixer1234: Yeah, I've done this before with other data sets and it worked perfectly. I don't remember updating Excel recently, though. – puggsoy Nov 5 '14 at 6:16
  • 1
    @puggsoy, INTERCEPT gives you the y-intercept of the trend line not the first data point. – Mr. Mascaro Nov 5 '14 at 16:37
2

I would guess that the format of the trendline number is just funny.

Under layout tab on ribbon select 4pcmm trendline label and click format selection.

In the box that comes out go to number (up by default) and it should have general selected. Change this to scientific (or whatever you want) and set the demimal places to 4. It should change your formula to

4.5381+E03+1.4500+E06 or something similar. If you only want one number behind the E change format code to 0.0000E+0 and then click add.

Number format with less decimals (0 or 1) would also be a good selection

| improve this answer | | | | |
  • I was a bit confused with your instructions at first, since I don't have a Layout tab (at least not in Excel 2007). However I took a look at changing the format of the trendline equation (right-clicking on it and choosing "Format Trendline Label) and then did what you said. It now shows the correct equation, thank you! I do wonder why it was being funny though, all previous times it worked fine. – puggsoy Nov 6 '14 at 0:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.