Weighted Linear Regression - Chromatography Forum

Weighted Linear Regression

Discussions about GC and other "gas phase" separation techniques.

4 posts Page 1 of 1
OK, technically not a chromatography question as I'm working with ICPMS data, but...

I am trying to reproduce a MassHunter calibration curve generated in Excel.

The curve is linear regression weighted using 1/SD^2, not forced through origin

I can easily reproduce the unweighted curve, by either plotting a scatter graph of the data and letting excel do the regression, or by manually calculating the regression slope myself.

How can I apply the weighting?

I know that excel does not have a function to fit a trendline using weighting. There may be an add-in around, but I don't want to use an automatic function - I want to learn and get a feel for how weighting works.

I want to apply the weighting manually to generate the new points, but I don't know what parameters to apply the weighting to.

----suffers separation anxiety----
You can get a better fit for ICP-MS data by using a log-linear calibration. Use the log of concentration (X) in ppb versus Log(y)=[slope(Log[(X-bias)/S])+intercept]. If you plot Log(X) versus the intensity ratio=Log[(X-bias)/S] its a straight line for 6 orders of magnitude.

S is the internal standard intensity in counts/second. Bias is the intensity of the lowest counts/second sample in the run. Usually, that is the cal blank.

Look up the first chapter of 'Rare Earth Elements in Groundwater Systems' in your local library...
The Analysis of Picogram Quantities of Rare Earth Elements in Natural Waters. You want page 28.

However, if you really need to apply weighting and want to use Excel then get Draper and Smith 'Applied Regression Analysis' from your local library. Its got some very nice formulas that preserve computational accuracy in a spreadsheet.
Excellent - thanks for the references.

I'm not convince that 1/SD^2 is the best weighting for the application, but I'm really just trying to replicate the maths using excel or VBA
----suffers separation anxiety----
----suffers separation anxiety----
4 posts Page 1 of 1

Who is online

In total there are 10 users online :: 0 registered, 0 hidden and 10 guests (based on users active over the past 5 minutes)
Most users ever online was 234 on Tue Feb 06, 2018 7:33 am

Users browsing this forum: No registered users and 10 guests