How to do weighted linear regression excel

broken image

One would hope to have a best fit line that is closer to the points on the bottom left corner of the graph, as they are the most 'precise'. I have set the size of the error bar to be proportional to the value of xdata.

broken image

In the graph above, the smaller the horizontal error bar on a point is, the more 'precise' it is. I have included a screenshot and a more detailed explanation below to illustrate. How can I calculate a best fit curve for my data that reflects this idea that more 'precise' points should have greater weighting when determining the best fit curve? However, in the data set I'm using, some values of xdata are more 'precise' than others, meaning the trend line ought to be closer to these points, and it's okay if the trend line is far from points that are 'imprecise'. Traditionally, Libreoffice Calc calculates the optimal trend line using a method which gives each data point equal weighting when figuring out which trend line best fits the data points. I have got a set of experimental data called xdata and ydata that I want to approximate with a trend line.

broken image