So I have a set of 9 x,y values, and I need to find the gradient/slope of the data, AND its associated error.
Without the error, I would've used Excels LINEST function, but as the errors in my y values are large (no x value errors) I need LINEST to take these into account.
How would I do this?
Thanks!
First of all, let me precise that I am not a statistician but, as a physicist, I faced this kind of problems quite often.
What you need is to use weighted least square regression. Assuming a linear model, then what you want is to minimize $$SSQ = \sum _{i=1}^n w(i) (y(i)-a-b x(i))^2$$ in which $w(i)$ is the weight allocated to data point $i$.
As usual, we shall compute the derivatives of $SSQ$ with respect to parameters $a$ and $b$ and set these derivatives equal to zero. As for the classical least square regression, you will need to solve for $a$ and $b$ $$S_1=a S_2 + b S_3$$ $$S_4=a S_3+b S_5$$ in which $$S_1=\sum _{i=1}^n w(i) y(i)$$ $$S_2=\sum _{i=1}^n w(i)$$ $$S_3=\sum _{i=1}^n w(i) x(i)$$ $$S_4=\sum _{i=1}^n w(i) x(i) y(i)$$ $$S_5=\sum _{i=1}^n w(i) x(i)^2$$ You should notice that if you set all the $w(i)$ equal to $1$, you get the classical equations. So, the problem is very simple : two equations for the two unknowns $a$ and $b$. All of that would be very easily done under Excel.
What is left is the choice of the $w(i)$. In your case, I should select $$w(i)=\frac{1}{\text{$\Delta $y}(i)}$$ or, may be better according to my experience, $$w(i)=\frac{1}{\text{$\Delta $y}(i)^2}$$ For sure, all your data point must have an associated $\Delta y(i)$.
As you see, it is simple.
Let me know how this works with your data (try both weightings).