Search code examples
excelequationtrendline

Plugging in the trendline equation in excel gives values different than the actual line on the graph

I apologize if this question should be somewhere else but I could not find a seperate section for excel related questions.

I am recording data readings from a sensor based on the water volume in a tank. After collecting 5 data points for each volume ( 0ml, 5ml, 10ml,...), I found the average and plotted the sensor readings vs. the volume. Now, although it is a linear relationship as shown in the image below, if I try to find the expected sensor reading or volume by plugging in the number in the equation, I get a totally different number than the expected output from the graph and from the sensor output. Strangely, if I eye-ball it, it gives a value similar to the sensor readings. Am I missing something?

Screenshot

For example, in the image below; if you plug in 7ml instead of x you get a value of about 45 but the actual value on the trendline is somewhere near 57.

My question: Why does the equation, which is based on the values from the sensor, give a different result than expected if I plug in the numbers manually?

Thank you for your time and help in advance.

Solution

  • The chart itself gives the clue to the issue: the x-axis values are evenly spaced even though the numbers they represent are not.

    The OP has created a line chart, where the first column of data is used as the column labels (hence the ‘ml’ suffix). Hence the ‘0 ml’ label is considered to be x=1, and hence the y value is around 65. The last label ‘50 ml’ is x=6, ie a y value of around 48.7 (which corresponds to the chart).

    The OP needs to create an x-y scatter plot instead, where the first column contains numeric values, not labels.