- On 1 Sep 2006 at 11:34:31, "Tata, Prasad N" (Prasad.Tata.at.TycoHealthcare.com) sent the message

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] - On 1 Sep 2006 at 15:11:26, "David S. Farrrier" (DFarrier.-at-.SummitPK.com) sent the message

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 - On 2 Sep 2006 at 09:58:41, "Ma Guangli" (guanglima.-at-.gmail.com) sent the message

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] - On 8 Sep 2006 at 06:41:10, bhagirath patel (bhagirathptl.-at-.yahoo.com) sent the message

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 - On 9 Sep 2006 at 16:43:17, Xiaodong Shen (shenxiaodong11.-a-.yahoo.com) sent the message

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 - On 11 Sep 2006 at 11:54:17, "Yung-jin Lee" (yjlee168.-at-.kmu.edu.tw) sent the message

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

Want to post a follow-up message on this topic? If this link does not work with your browser send a follow-up message to PharmPK@boomer.org with "WLS using Excel" as the subject

PharmPK Discussion List Archive Index page

Copyright 1995-2010 David W. A. Bourne (david@boomer.org)