Chromatography Forum

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

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

I found the answer here:

https://pdfs.semanticscholar.org/9584/3 ... e97abd.pdf

Thanks