Author 
Message 
anthonychan


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

Hi
Just 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 regards
Anthony Chan





PhDemon


Joined: Wed Jun 26, 2013 10:44 am Posts: 461 Location: NewcastleuponTyne

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


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 regards
Anthony





PhDemon


Joined: Wed Jun 26, 2013 10:44 am Posts: 461 Location: NewcastleuponTyne

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





