Back to the Top
Can anyone advise me how to construct a Weighted Least Square
analysis (analysis and plotting) using Excel? I am planning to do WLS
using various regression fits thought I will use a simple program
like excel.
Prasad NV Tata, Ph.D.
Mallinckrodt, Inc.
675 McDonnell Blvd.
Saint Louis, MO 63134
Tel: (314) 654-5325
Fax: (314) 654-9325
e-mail: prasad.tata.-at-.tycohealthcare.com
[An earlier sugestion was to use the solver function. You would set
up columns for the Time, Obs Conc, Calc Conc, Weight. Calculate
weight values with your favorite formula (1/Calc etc.) or enter
specific weights. Now set-up a column for weighted residual squared =
wt * (Cobs-Ccal) ** 2. Sum that column = sum of weighted, squared
residuals (WSS). The solver comes in by changing or adjusting the
parameters used to calculate Calc Conc, to minimize the WSS - db]
Back to the Top
Prasad,
You query and the thread "Weighting factors with Excel" are similar
in scope, so I hope you don't mind me repeating my comment concerning
the latter, as it seems to apply to your query as well.
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
Back to the Top
Dear Prasad NV Tata,
You can construct a WLS analysis according so many solutions.
If you want to plot data and regression lines in one graph using
Excel, you should calculate all of points on the lines.
For example, you have X, Y data. Then you get two regression
lines: y=a1*x+b1 and y=a2*x+b2.
Via these two functions, we could get the plotting data ((X1,
Y1), (X2, Y2)) at a constant interval (0.1 or 0.01).
XY Scatter in Excel could generate such a graph. Three series
((X,Y), (X1,Y1), (X2, Y2)) are added to the graph. The properties of
(X1, Y1), (X2, Y2) can be adjuested to make them look like lines.
I am sorry that my Excel is chinese version. I can not describe
more details.
Ma Guangli
[I put together a page with a small Excel file and instructions. Also
files for fitting the same data with Boomer with instructions. Of
course any other nonlinear regression program could perform this
anlaysis. See http://www.boomer.org/pkin/eg/solverEG/solver.html - db]
Back to the Top
hi.
in excel, you need to enter data in different colums. i.e.
conc.
response
1 3
2 6
3 9
4 12
5 15
now select both of the colums, (not the title i.e. conc and
responce). now go to graph selection menu. then set the graph type as
xy scatter. after that select the sub type as compares pairs of
values, then follow further instructions. as you see the graph, click
the line (right click), you will see the option "ADD TRENDLINES" go
to that option, select different option i.e linear, power, log etc.
then in same tool bar go to options, there is option for add
equations and R2 value. you will get co-relation graph, as same you
can program your data in excel with data options.
hope this will help .
regards,
bhagirath
Back to the Top
The following message was posted to: PharmPK
Hi,
I did what you said including R2 and equation, but how
you can including weighing factor in your linear
regression?
Thanks.
Xiaodong
Back to the Top
The following message was posted to: PharmPK
Hi,
There is a website link with a .doc file which
may be helpful to solve this question.
http://finance.sauder.ubc.ca/courses/comm472/ARCH.doc
regards,
YJ
PharmPK Discussion List Archive Index page
Copyright 1995-2010 David W. A. Bourne (david@boomer.org)