﻿ Weighted Linear Regression - Chromatography Forum

## Weighted Linear Regression

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

### Weighted Linear Regression

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.

Thanks
----suffers separation anxiety----

### Re: Weighted Linear Regression

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.

### Re: Weighted Linear Regression

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----

### Re: Weighted Linear Regression

----suffers separation anxiety----

### Who is online

In total there are 9 users online :: 0 registered, 0 hidden and 9 guests (based on users active over the past 5 minutes)
Most users ever online was 599 on Tue Sep 18, 2018 9:27 am

Users browsing this forum: No registered users and 9 guests

### Latest Blog Posts from Separation Science

Separation Science offers free learning from the experts covering methods, applications, webinars, eSeminars, videos, tutorials for users of liquid chromatography, gas chromatography, mass spectrometry, sample preparation and related analytical techniques.

Subscribe to our eNewsletter with daily, weekly or monthly updates: Food, Environmental, (Bio)Pharmaceutical, Bioclinical, Liquid Chromatography, Gas Chromatography and Mass Spectrometry.