I realise this doesn't seem accurate but it is not possible to have a value higher than zero at the zero point for my data therefore my trend line has to go through the origin. I can't seem to find how to force it through that point so if I can't do it I have to redo everything in excel which I would really like to avoid. Any help would be greatly appreciated!
MacBook Air
You can create the zero-intercept trend line in your table and plot it as a series. In the screenshot below, the blue line with the datapoints is your actual data, the thick blue line is the real trend line, the green line is your zero-intercept trend line. All on a scatter chart.
The slope in cell E2 is calculated as =LINEST(C2:C6,B2:B6,0)
The trend line column is =B2×E$2
You can create the zero-intercept trend line in your table and plot it as a series. In the screenshot below, the blue line with the datapoints is your actual data, the thick blue line is the real trend line, the green line is your zero-intercept trend line. All on a scatter chart.
The slope in cell E2 is calculated as =LINEST(C2:C6,B2:B6,0)
The trend line column is =B2×E$2
Numbers trend lines represent the data you are charting and can't be wiggled to suit the rules you are working to. Have you thought about adding a 0,0 data point to your series? That might get you closer than you are now.
J.
Wouldn't it be a bit of a fib to say your modified trend line represents the data?
My approach would be to see where the actual linear estimate crosses the vertical axis for X=0, then subtract that amount from all my data and re-chart. Or, just annotate your chart to the effect that your measurements seem to have an offset.
J.
That's why I said it doesn't seem accurate but it is. I am subtracting the values as I am using the graph as a calibration graph to find unknown x values from y values. However this gives me some negative numbers which is not a possible value. I cannot have negative amounts of drug in sample which is why I need the trendline to go through zero but can't figure out how to do it.
How do I force a linear trend line through the origin (0,0)?
