# PharmPK Discussion - WLS using Excel

PharmPK Discussion List Archive Index page
• On 1 Sep 2006 at 11:34:31, "Tata, Prasad N" (Prasad.Tata.at.TycoHealthcare.com) sent the message
`Can anyone advise me how to construct a Weighted Least Squareanalysis (analysis and plotting) using Excel? I am planning to do WLSusing various regression fits thought I will use a simple programlike excel.Prasad NV Tata, Ph.D.Mallinckrodt, Inc.675 McDonnell Blvd.Saint Louis, MO 63134Tel: (314) 654-5325Fax: (314) 654-9325e-mail: prasad.tata.-at-.tycohealthcare.com[An earlier sugestion was to use the solver function. You would setup columns for the Time, Obs Conc, Calc Conc, Weight. Calculateweight values with your favorite formula (1/Calc etc.) or enterspecific weights. Now set-up a column for weighted residual squared =wt * (Cobs-Ccal) ** 2. Sum that column = sum of weighted, squaredresiduals (WSS). The solver comes in by changing or adjusting theparameters used to calculate Calc Conc, to minimize the WSS - db]`
Back to the Top

• On 1 Sep 2006 at 15:11:26, "David S. Farrrier" (DFarrier.-at-.SummitPK.com) sent the message
`Prasad,You query and the thread "Weighting factors with Excel" are similarin scope, so I hope you don't mind me repeating my comment concerningthe latter, as it seems to apply to your query as well.PK Solutions is an Excel-based pharmacokinetics data analysis programthat makes PK analysis easy.The demo copy of PK Solutions illustrates the setup of a nonlinearregression analysis using the Solver add-in. To see this, downloadthe demo from www.SummitPK.com/pksolutions/pksolutions.htm (pre andpost 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 demoillustrates the software in detail, but is disabled from performingpersonal data sets.Regards,David                                /\         /\        SummitPK.com       /\  /    \ /\  /   \                          /   /      /   /\   /  \===============================================David S. Farrier, Ph.D.          Phone:   970-249-1389Summit Research Services         Fax:    970-249-136068911 Open Field Dr.             Email:   DFarrier.-a-.SummitPK.comMontrose, CO 81401               Web:      http://www.SummitPK.com`
Back to the Top

• On 2 Sep 2006 at 09:58:41, "Ma Guangli" (guanglima.-at-.gmail.com) sent the message
`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 usingExcel, you should calculate all of points on the lines.     For example, you have X, Y data. Then you get two regressionlines: 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 describemore details.Ma Guangli[I put together a page with a small Excel file and instructions. Alsofiles for fitting the same data with Boomer with instructions. Ofcourse any other nonlinear regression program could perform thisanlaysis. See http://www.boomer.org/pkin/eg/solverEG/solver.html - db]`
Back to the Top

• On 8 Sep 2006 at 06:41:10, bhagirath patel (bhagirathptl.-at-.yahoo.com) sent the message
`hi.in excel, you need to enter data in different colums. i.e.conc.response1   32   63   94   125   15now select both of the colums, (not the title i.e. conc andresponce). now go to graph selection menu. then set the graph type asxy scatter. after that select the sub type as compares pairs ofvalues, then follow further instructions. as you see the graph, clickthe line (right click), you will see the option "ADD TRENDLINES" goto that option, select different option i.e linear, power, log etc.then in same tool bar go to options, there is option for addequations and R2 value. you will get co-relation graph, as same youcan program your data in excel with data options.hope this will help .regards,bhagirath`
Back to the Top

• On 9 Sep 2006 at 16:43:17, Xiaodong Shen (shenxiaodong11.-a-.yahoo.com) sent the message
`The following message was posted to: PharmPKHi,I did what you said including R2 and equation, but howyou can including weighing factor in your linearregression?Thanks.Xiaodong`
Back to the Top

• On 11 Sep 2006 at 11:54:17, "Yung-jin Lee" (yjlee168.-at-.kmu.edu.tw) sent the message
`The following message was posted to: PharmPKHi,     There is a website link with a .doc file whichmay be helpful to solve this question.http://finance.sauder.ubc.ca/courses/comm472/ARCH.docregards,YJ`
Back to the Top

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

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