## MS Excel chart help

Discussions on everything related to the software, electronic, and mechanical components of information systems and instruments.

### MS Excel chart help

There are enough scientists here that might be able to help me with this or know where I might get help to figure this out.

I have a column with dates and another column with price.

I have then charted them with the dates as Y and price as X (Author note: to avoid future confusion, zetreque meant dates as X and price as Y)

I then did a trend line and obtained a formula.

How to use the formula is my question.

To get the date value to plug into the formula I tried using =DATEVALUE("5/20/18") and then plugging the number I get from that in for X but it's not giving me a Y value that makes sense.
Last edited by zetreque on May 20th, 2017, 11:07 pm, edited 1 time in total.
Reason: Author Note

zetreque
Forum Moderator

Posts: 3345
Joined: 30 Dec 2007
Location: Paradise being lost to humanity
Blog: View Blog (7)

### Re: MS Excel chart help

Doing linear regression analysis with dates as dates doesn't make sense to me. Instead, I would convert dates to time (t), relative to some past date used as a reference (t=0). Now you will have numbers (let's say in units of days or years, depending on how spaced your dates are) that you can do linear regression on (make your time dimension X and your price dimension Y). To find the price (Y) at some new date (X), you would again convert that date into time (t= new date - reference date), plug the number in your equation, and find the predicted price.

BioWizard

Posts: 12723
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)
 SciameriKen liked this post

### Re: MS Excel chart help

I read about DateValue (didn't know it) and I suspect it might also work for you (not sure though cause I've not used it before). Did you convert your dates to datevalues before fitting the data?

BioWizard

Posts: 12723
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)

### Re: MS Excel chart help

Oh, wait, I just realized that your problem may be much simpler than what I was thinking. You seem to have mixed up your X and Y. If you charted your dates as Y and your price as X, why are you plugging your datevalue into X of your trendline formula?

Just do it again with your dates as X and your prices as Y, get the trendline formula, then plug your datevalue into the X and that should give you the price.

BioWizard

Posts: 12723
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)

### Re: MS Excel chart help

Not sure what I am doing wrong here.

y = (59.423 * 43240) - 2000000
y = 569450.5

zetreque
Forum Moderator

Posts: 3345
Joined: 30 Dec 2007
Location: Paradise being lost to humanity
Blog: View Blog (7)

### Re: MS Excel chart help

Switching axis worked but I don't know why.

43240 = .0146x + 40576

x = 183,724

zetreque
Forum Moderator

Posts: 3345
Joined: 30 Dec 2007
Location: Paradise being lost to humanity
Blog: View Blog (7)

### Re: MS Excel chart help

The strange thing is the =Forecast function works which appears to do the same thing.

=FORECAST(X, Yrange, Xrange)
https://excel.tips.net/T002573_Using_th ... ction.html

zetreque
Forum Moderator

Posts: 3345
Joined: 30 Dec 2007
Location: Paradise being lost to humanity
Blog: View Blog (7)

### Re: MS Excel chart help

zetreque » 20 May 2017 02:39 pm wrote:Switching axis worked but I don't know why

Because you were plugging the date value into the variable you used to model the price. Which ever variable you assign to the date is where you need to plug in dates to forecast price. If you set X for dates, then you plug dates into the X of the equation. If you set Y to dates, then you plug dates into the Y. Originally you assigned Y to dates but then you plugged your date into the X. Do you get that part?

BioWizard

Posts: 12723
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)

### Re: MS Excel chart help

zetreque » 20 May 2017 03:03 pm wrote:The strange thing is the =Forecast function works which appears to do the same thing.

=FORECAST(X, Yrange, Xrange)
https://excel.tips.net/T002573_Using_th ... ction.html

Yes, the FORECAST function basically does the same thing in a single step.

BioWizard

Posts: 12723
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)

### Re: MS Excel chart help

zetreque » Sat May 20, 2017 11:22 am wrote:Not sure what I am doing wrong here.

y = (59.423 * 43240) - 2000000
y = 569450.5

ch.jpg

But here I have dates assigned to X and when I plug a date into X it doesn't give me the right Y value based on the formula excel gave me.

EDIT: I apologize. I spoke wrong in the original OP mixing up the X and Y.

zetreque
Forum Moderator

Posts: 3345
Joined: 30 Dec 2007
Location: Paradise being lost to humanity
Blog: View Blog (7)

### Re: MS Excel chart help

Right :) I didn't notice it the first time I read your post either.

BioWizard

Posts: 12723
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)

### Re: MS Excel chart help

Don't know why that formula doesn't work though but since forecast function does it's not important.

The next thing I am trying to figure out is how to have certain coordinates show up a different color or symbol when charting data in excel 2013.

zetreque
Forum Moderator

Posts: 3345
Joined: 30 Dec 2007
Location: Paradise being lost to humanity
Blog: View Blog (7)

### Re: MS Excel chart help

zetreque » 20 May 2017 04:41 pm wrote:Don't know why that formula doesn't work though but since forecast function does it's not important.

You're killing me lol. Send me your data.

BioWizard

Posts: 12723
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)

### Re: MS Excel chart help

Zet, the problem was that the formula you were using had a huge error in the intercept. When displaying the equation on the graph, Excel was rounding the intercept to the nearest million! LOL

I solved the linear regression using Excel's data analysis module, which makes all the details of the regression explicit, and that allowed me to see that the exact value of the intercept is -2404952.91880844 and NOT 2000000. Try it now.

BioWizard

Posts: 12723
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)

### Re: MS Excel chart help

I get \$170940.3612, which - as expected - plots smack on the fitted line.

BioWizard

Posts: 12723
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)

### Re: MS Excel chart help

By the way, your comment below was my clue that Excel was displaying something incorrectly about the equation, since it suggested that FORECAST was avoiding the issue by directly acquiring the equation parameters from the regression.

zetreque » 20 May 2017 04:41 pm wrote:Don't know why that formula doesn't work though but since forecast function does it's not important.

BioWizard

Posts: 12723
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)

### Re: MS Excel chart help

BioWizard » Sat May 20, 2017 4:53 pm wrote:Zet, the problem was that the formula you were using had a huge error in the intercept. When displaying the equation on the graph, Excel was rounding the intercept to the nearest million! LOL

I solved the linear regression using Excel's data analysis module, which makes all the details of the regression explicit, and that allowed me to see that the exact value of the intercept is -2404952.91880844 and NOT 2000000. Try it now.

biow_Page_1.png

Wow. Good job! Gotta watch out for those rounding errors. Good thing I'm not sending a satellite into space ;)

zetreque
Forum Moderator

Posts: 3345
Joined: 30 Dec 2007
Location: Paradise being lost to humanity
Blog: View Blog (7)

### Re: MS Excel chart help

I should have realized that the nice round number for the intercept was too simple.

Good to know.
How to change decimal places for trendline formula label
https://support.microsoft.com/en-us/help/282135/how-to-display-more-digits-in-trendline-equation-coefficients-in-excel

As far as my other challenge to display different data points in different symbols. I can break the columns up "Select Data" and add the different segments. The unfortunate thing about doing it that way is that when I sort data, excel doesn't keep track. But I'm good for now.

zetreque
Forum Moderator

Posts: 3345
Joined: 30 Dec 2007
Location: Paradise being lost to humanity
Blog: View Blog (7)