Charting Help!

ExcelHELP!

New Member
Joined
Jan 20, 2005
Messages
6
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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.)
 

Forum statistics

Threads
1,147,510
Messages
5,741,574
Members
423,668
Latest member
Audorin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top