Back to the Top
The following message was posted to: PharmPK
Does anybody know how to using excel to do linear
calibration with a weighing factor 1/x or 1/x^2?
Thanks
Xiaodong
Back to the Top
The following message was posted to: PharmPK
Dear Xiadoing,
In excel you just can calculate the sum of squares yourself and use the
solver for optimization. Then it is just a matter of dividing the
residuals by x^2 before squaring. (Prof. Derendorf showed me this trick
to get PK fits in excel) But be aware that Excel's solver is not optimal
for PK(-PD) problems.
Best regards,
Jeroen
J. Elassaiss-Schaap
Scientist PK/PD
Organon NV
PO Box 20, 5340 BH Oss, Netherlands
Phone: + 31 412 66 9320
Fax: + 31 412 66 2506
e-mail: jeroen.elassaiss.at.organon.com
[At one point Solver was an optionally installed 'add-in' so you may
need to revisit your installation disks to set it up. I found it in
the Tools menu of the Mac version - db]
Back to the Top
The following message was posted to: PharmPK
Hello Xiaodong,
The most elegant way is by using the matrix calculation functions in
excel.
The X matrix has two columns with 1 in the first column and the x
values in
the second. The Y matrix has one column with the y values. The weight
matrix
is a diagonal matrix with the 1/x or 1/x2 values. Then use this
formula to
calculate b and a (final matrix is one column, two rows). Don't
forget to
use array formula's.
=MMULT((MINVERSE(MMULT(TRANSPOSE(X);MMULT(W;X))));MMULT(TRANSPOSE
(X);MMULT(W;Y)))
Best regards,
Kris
--
Kris Baert, DVM, PhD
Pharmacology, Toxicology, Biochemistry and Organ Physiology
Veterinary Medicine - Ghent University
Back to the Top
The following message was posted to: PharmPK
Dear Xiaodong,
you can use, with prudence, the Excel "SOLVER" tool.
An example of spreadsheets setup and calculations for nonlinear
regression (obviously applicable to linear regression problems too)
has been described in TiPS more than 10 years ago by Bowen and Jerman:
Bowen WP, Jerman JC. Nonlinear regression using spreadsheets. Trends
Pharmacol Sci. 1995 Dec;16(12):413-7.
Best regards
Stefano
Dr. Stefano Porzio
Inpharzam Ricerche S.A. ZambonGroup
Taverne
Switzerland
Back to the Top
The following message was posted to: PharmPK
You could design your own spreadsheets employing Excel, taking into
account
the following references:
Statistics for analytical chemistry, J.C Millar and Jn Millar or
Selecting the best curve fit, LC.GC Europe, 17 (3), 138-143,2004 Linear
regression for calibration lines revisited: weighing schemes for
bioanalytical methods, J. Chromatography B 774, 215-222,2002.
Hoping to be of help
Valeria Pongelli
Back to the Top
Xiaodong,
PK Solutions is an Excel-based pharmacokinetics data analysis program
that makes PK analysis easy.
The demo copy of PK Solutions illustrates the setup of a nonlinear
regression analysis using the Solver add-in. To see this, download
the demo from www.SummitPK.com/pksolutions/pksolutions.htm (pre and
post Excel XP versions are available). Open the workbook to the
"Curve Fit" spreadsheet tab. Click the "Curve Fitting Procedure"
button for an explanation of how this works. Note that the demo
illustrates the software in detail, but is disabled from performing
personal data sets.
Regards,
David
/\ /\
SummitPK.com /\ / \ /\ / \
/ / / /\ / \
=================================================
David S. Farrier, Ph.D. Phone: 970-249-1389
Summit Research Services Fax: 970-249-1360
68911 Open Field Dr. Email: DFarrier.-a-.SummitPK.com
Montrose, CO 81401 Web: http://www.SummitPK.com
PharmPK Discussion List Archive Index page
Copyright 1995-2010 David W. A. Bourne (david@boomer.org)