MS Excel chart help

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

MS Excel chart help

Postby zetreque on May 20th, 2017, 12:39 am 

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
User avatar
zetreque
Forum Moderator
 
Posts: 3563
Joined: 30 Dec 2007
Location: Paradise being lost to humanity
Blog: View Blog (3)


Re: MS Excel chart help

Postby BioWizard on May 20th, 2017, 6:44 am 

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.
User avatar
BioWizard
Forum Administrator
 
Posts: 12762
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)
SciameriKen liked this post


Re: MS Excel chart help

Postby BioWizard on May 20th, 2017, 8:08 am 

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?
User avatar
BioWizard
Forum Administrator
 
Posts: 12762
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)


Re: MS Excel chart help

Postby BioWizard on May 20th, 2017, 8:12 am 

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.
User avatar
BioWizard
Forum Administrator
 
Posts: 12762
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)


Re: MS Excel chart help

Postby zetreque on May 20th, 2017, 3:22 pm 

Not sure what I am doing wrong here.

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

ch.jpg
User avatar
zetreque
Forum Moderator
 
Posts: 3563
Joined: 30 Dec 2007
Location: Paradise being lost to humanity
Blog: View Blog (3)


Re: MS Excel chart help

Postby zetreque on May 20th, 2017, 3:39 pm 

Switching axis worked but I don't know why.

ch2.jpg


43240 = .0146x + 40576

x = 183,724
User avatar
zetreque
Forum Moderator
 
Posts: 3563
Joined: 30 Dec 2007
Location: Paradise being lost to humanity
Blog: View Blog (3)


Re: MS Excel chart help

Postby zetreque on May 20th, 2017, 4:03 pm 

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
User avatar
zetreque
Forum Moderator
 
Posts: 3563
Joined: 30 Dec 2007
Location: Paradise being lost to humanity
Blog: View Blog (3)


Re: MS Excel chart help

Postby BioWizard on May 20th, 2017, 4:30 pm 

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?
User avatar
BioWizard
Forum Administrator
 
Posts: 12762
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)


Re: MS Excel chart help

Postby BioWizard on May 20th, 2017, 4:34 pm 

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.
User avatar
BioWizard
Forum Administrator
 
Posts: 12762
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)


Re: MS Excel chart help

Postby zetreque on May 20th, 2017, 4:42 pm 

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.
User avatar
zetreque
Forum Moderator
 
Posts: 3563
Joined: 30 Dec 2007
Location: Paradise being lost to humanity
Blog: View Blog (3)


Re: MS Excel chart help

Postby BioWizard on May 20th, 2017, 5:35 pm 

Right :) I didn't notice it the first time I read your post either.
User avatar
BioWizard
Forum Administrator
 
Posts: 12762
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)


Re: MS Excel chart help

Postby zetreque on May 20th, 2017, 5:41 pm 

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.
User avatar
zetreque
Forum Moderator
 
Posts: 3563
Joined: 30 Dec 2007
Location: Paradise being lost to humanity
Blog: View Blog (3)


Re: MS Excel chart help

Postby BioWizard on May 20th, 2017, 5:43 pm 

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.
User avatar
BioWizard
Forum Administrator
 
Posts: 12762
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)


Re: MS Excel chart help

Postby BioWizard on May 20th, 2017, 8:53 pm 

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
User avatar
BioWizard
Forum Administrator
 
Posts: 12762
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)


Re: MS Excel chart help

Postby BioWizard on May 20th, 2017, 8:55 pm 

I get $170940.3612, which - as expected - plots smack on the fitted line.
User avatar
BioWizard
Forum Administrator
 
Posts: 12762
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)


Re: MS Excel chart help

Postby BioWizard on May 20th, 2017, 9:03 pm 

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.
User avatar
BioWizard
Forum Administrator
 
Posts: 12762
Joined: 24 Mar 2005
Location: United States
Blog: View Blog (3)


Re: MS Excel chart help

Postby zetreque on May 20th, 2017, 10:30 pm 

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 ;)
User avatar
zetreque
Forum Moderator
 
Posts: 3563
Joined: 30 Dec 2007
Location: Paradise being lost to humanity
Blog: View Blog (3)


Re: MS Excel chart help

Postby zetreque on May 20th, 2017, 10:53 pm 

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.
User avatar
zetreque
Forum Moderator
 
Posts: 3563
Joined: 30 Dec 2007
Location: Paradise being lost to humanity
Blog: View Blog (3)



Return to Computers

Who is online

Users browsing this forum: No registered users and 1 guest