Charting Help!

ExcelHELP!

New Member
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?

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

PA HS Teacher

Well-known Member
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.)

Replies
2
Views
1K
Replies
1
Views
564
Replies
1
Views
230
Replies
1
Views
264
Replies
5
Views
2K

1,181,128
Messages
5,928,259
Members
436,594
Latest member
asifm0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back