-1

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.

DailyReader
  • 43
  • 1
  • 8
  • So show the equation and then how you completed and resolved it. – Solar Mike Dec 28 '21 at 13:14
  • The equation is the trendline equation on the graph above. Try this: Plug in 5 in the equation of the tile. It gives you about 52. However, if you try to eyeball it from the graph itself and see where x=5 intercepts the line of the equation, you see it intercepts at about 62. So why do we have such a huge difference? – DailyReader Dec 28 '21 at 13:40
  • 1
    I suggested you provided the detail so we could check your maths… since you have not I won’t continue. – Solar Mike Dec 28 '21 at 13:56
  • You need to use all 15 significant digits from the formula on the graph. You are only showing three. Also show the equation as you are using it on the worksheet. – Ron Rosenfeld Dec 28 '21 at 14:18
  • @SolarMike I thought I provided the necessary details in my comment. The rest of th data is just 5 readings for each volume and the average of each of these 5 readings. Then the plot you see. Nothing more. Thank you for your reply and attempt to help anyway. – DailyReader Dec 30 '21 at 06:24
  • @RonRosenfeld thank you for your reply. I tried to change the digits. It does not change much. The only equation I have is the one on the worksheet. I do not use it anywhere else. I just tested it by plugging in a number and seeing if it matches the line on the graph. Thank you anyway! – DailyReader Dec 30 '21 at 06:26

1 Answers1

2

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.

DS_London
  • 3,644
  • 1
  • 7
  • 24