Charting Help!

[ExcelHELP!]

I've created a scatter plot with absorbance on the y-axis and concentration on the x-axis. I've added a linear trendline.

Now I have a set of absorbance values from a separate experiment and I need to calculate x-values based on where along the x-axis these new y-axis values hit the trendline. Is there a way I can tell the exact value?

 

[PA HS Teacher]

Use the Equation from your line of best fit:

Y=MX + B, where M and B are constants generated by the trend line

Which means
X = (Y - B)/M

In English,
TheoreticalConcentration = (Absorbance - Absorbance when Concentration is Zero) / Slope of Trend Line

The physical significance of the slope is that, for every increase of 1 concentration unit, the Absorbance changes by M Absorbance Units.

To apply this in Excel, make an additional column for the Theoretical Concentration and enter a formula of the following form:

=(A1 - .34)/.21 Copy and paste this down.

I made up values for the slope and y-intercept of the line. Use your own.
I also assumed your new Absorbance values were in Column A.

Keep in mind, This is a theoretical prediction for the value of X, based on your previous data. To get actual values for Concentration, you would need to make actual measurements.

(I appologize if I have slipped into my teacher voice.)