It is currently Mon Sep 25, 2017 10:08 pm

 4 posts • Page 1 of 1
Author Message
anthonychan
 Post subject: Excel trend lines  |  Posted: Wed Jan 04, 2017 4:47 pm

Joined: Thu Nov 03, 2016 10:25 am
Posts: 2

 HiJust a quick query regarding trend lines in excel. So, I have some chemistry data which is catalyst additions against gel times. So increasing additions of catalyst reduces the gel times. I have several data points and have plotted them on a line graph in excel with times on the x axis and catalyst additions on the y axis. I have added a logarithmic trend line and the resulting equation is -0.657ln(x)+1.2912.The issue I'm having is, when trying to use this equation to predict certain catalyst levels for specific times, the answer it is giving me is incorrect. To check, I have inputted a known value for x, however it is not giving me the value for y I know to be correct. In fact, it's not even close.Any ideas on what the problem is?Kind regardsAnthony Chan
PhDemon
 Post subject: Re: Excel trend lines  |  Posted: Wed Jan 04, 2017 5:19 pm

Joined: Wed Jun 26, 2013 10:44 am
Posts: 499
Location: Newcastle-upon-Tyne

 How many data points do you have? Are the values you are trying to predict within the range of the data?The problem with logarithms is that even small errors can have a huge effect, if there are too few data points the trend line may not be a good fit to the data and give dodgy predictions. Also if you are trying to predict values outside the data range these inaccuracies can lead to huge differences. _________________"The big trouble with dumb bastards is that they are too dumb to believe there is such a thing as being smart"- Kurt Vonnegut, The Sirens of Titan
anthonychan
 Post subject: Re: Excel trend lines  |  Posted: Wed Jan 04, 2017 6:49 pm

Joined: Thu Nov 03, 2016 10:25 am
Posts: 2

 Hi Thanks for the reply. I have 7 data points. The values I am trying to predict are within the data range. I can roughly predict visually from the graph the x,y coordinate point that I require, however I wanted a more accurate way and thought I could use a trendline. I am not that skilled in excel so this is new to me. I was actually expecting to have to work out the equation myself!Kind regardsAnthony
PhDemon
 Post subject: Re: Excel trend lines  |  Posted: Wed Jan 04, 2017 8:09 pm

Joined: Wed Jun 26, 2013 10:44 am
Posts: 499
Location: Newcastle-upon-Tyne

 With only seven data points I'm guessing that maybe the line is not actually that good a fit to the data... If I were you I would go old school and work out your predicted values by drawing lines on the graph rather than trusting the equation... Another option might be that there are not enough decimal places in your trendline coefficients (this can be altered when picking which trendline you want). It may just be the rounding errors in reporting the coefficients are screwing you over. Try setting the number of decimal places to the max and use the equation that results from that to see if it is any better. _________________"The big trouble with dumb bastards is that they are too dumb to believe there is such a thing as being smart"- Kurt Vonnegut, The Sirens of Titan
 Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending
 4 posts • Page 1 of 1

Who is online
 Users browsing this forum: No registered users and 0 guests You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forum