FAQ
It is currently Wed Sep 20, 2017 11:14 am


Author Message
anthonychan
Post  Post subject: Excel trend lines  |  Posted: Wed Jan 04, 2017 4:47 pm

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

Offline
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


Top
PhDemon
Post  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

Offline
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


Top
anthonychan
Post  Post subject: Re: Excel trend lines  |  Posted: Wed Jan 04, 2017 6:49 pm

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

Offline
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


Top
PhDemon
Post  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

Offline
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


Top
Display posts from previous:  Sort by  
Print view

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


Delete all board cookies | The team | All times are UTC


This free forum is proudly hosted by ProphpBB | phpBB software | Report Abuse | Privacy