ࡱ> _a^ Sbjbj *zK77777KKKK4lK1z$M^77777Hp1K`01h@p7p,1 : PK Functions for Microsoft Excel Joel I. Usansky, Ph.D., Atul Desai, M.S. and Diane Tang-Liu, Ph.D. Department of Pharmacokinetics and Drug Metabolism Allergan, Irvine, CA 92606, U.S.A. 1. Introduction Microsoft Excel now has simple-to-use built-in pharmacokinetic functions just like the other functions you are familiar with, such as AVERAGE() and STDEV(). Eight pharmacokinetic (PK) functions which will simplify routine pharmacokinetic calculations in Microsoft Excel worksheets: Cmax, tmax, ElimRateConstant, Half_life AUC0_t, AUC0_inf, AUMC0_t and AUMC0_inf,. These eight PK Functions allow users to perform PK calculations on any Microsoft Excel worksheet. Users dont have to remember the mathematical formulae used to calculate these values and hence calculations will be less error-prone. In addition, since Microsoft Excel performs dynamic recalculation, users will get immediate feedback of results, providing efficient use of computer equipment and overall time savings. Users are referred to standard pharmacokinetic texts for further details on the mathematical basis of these functions (1-3). 2. Installation instructions Microsoft Office 2003 Download the PK Functions file and save onto your hard drive. Start Microsoft Excel 2003 and select Add-Ins from the Tools menu. Locate the file on your hard drive and make sure the two Add-Ins are enabled (i.e., checked). Microsoft Office 2007, 2010 Download the PK Functions file and save onto your hard drive. Start Microsoft Excel 2007 or 2010. Click the Microsoft Office button (the big circle in top left of the screen), and then click Excel Options. Click the Add-Ins category. In the Manage box, select Excel Add-ins, and then click Go. To load an Excel add-in, click Browse, and then locate the add-in on your drive. If the add-in is not currently installed on your computer, click OK to install it. To activate the Add-In, ensure both the PK1 and PK2 functions are checked. 3. The Functions 3.1 Cmax Calculates the maximum observed concentration in the conc_data range. Syntax Cmax(conc_data) where: conc_data is a reference to a range of cells (one column wide) containing concentration values Example Cmax(B10:B14) 3.2 Tmax Calculates the time point of the maximum concentration in the conc_data range. Syntax Tmax(conc_data,time_data) where: conc_data is a reference to a range of cells (one column wide) containing concentration values time_data is a reference to a range of cells (one column wide) containing time values Example Tmax(B10:B14,A10:A14) 3.3 ElimRateConst Calculates the elimination rate constant by regression of the semi-logarithmic concentration versus time data. Syntax ElimRateConst(conc_data,time_data) where: conc_data is a reference to a range of cells (one column wide) containing concentration values time_data is a reference to a range of cells (one column wide) containing the corresponding time values Example ElimRateConst(B12:B14,A12:A14) 3.4 Half_life Calculates the half-life by regression of the semi-logarithmic concentration versus time data. Syntax Half_life(conc_data,time_data) where: conc_data is a reference to a range of cells (one column wide) containing concentration values time_data is a reference to a range of cells (one column wide) containing the corresponding time values Example Half_life(B12:B14,A12:A14) 3.5 AUC0_t Calculates the area under the concentration versus time curve for data using the linear trapezoidal rule. The AUC is calculated between the time points in the time_data range. In pharmacokinetic calculations, the time points usually begin at time 0 and finish at the last quantifiable point. Syntax AUC0_t(conc_data,time_data) where: conc_data is a reference to a range of cells (one column wide) containing concentration values time_data is a reference to a range of cells (one column wide) containing the corresponding time values Examples AUC0_t(B10:B14,A10:A14) AUC0_t(B10:B14,$A$10:$A$14) 3.6 AUC0_inf Calculates the area under the concentration versus time curve from time 0 to time infinity using the linear trapezoidal rule. Syntax AUC0_inf(AUC0_time,Conc_last,ElimRateConstant) where: AUC0-time is a reference to a single cell containing the area under the curve from time 0 to time t. This is usually calculated using the AUC0_t function described above. Conc_last is a reference to a single cell containing the concentration of the last quantifiable time point ElimRateConst is a reference to a single cell containing the elimination rate constant Example AUC0_inf(A12,A13,A14) 3.7 AUMC0_t Calculates the area under the moment curve for data using the linear trapezoidal rule. The AUC is calculated between the time points in the time_data range. In pharmacokinetic calculations, the time points are usually from time 0 to the last quantifiable point. Syntax AUMC0_t(conc_data,time_data) where: conc_data is a reference to a range of cells (one column wide) containing concentration values time_data is a reference to a range of cells (one column wide) containing the corresponding time values Example AUMC0_t(B10:B14,A10:A14) 3.8 AUMC0_inf Calculates the area under the moment curve from time 0 to time infinity using the linear trapezoidal rule. Syntax AUMC0_inf(AUC0_time,Conc_last,Time_last,ElimRateConstant) where: AUMC0-time is a reference to a single cell containing the area under the curve from time 0 to time t. This is usually calculated using the AUMC0_t function described above. Conc_last is a reference to a single cell containing the concentration of the last quantifiable time point Time_last is a reference to a single cell containing the time of the last quantifiable time point ElimRateConst is a reference to a single cell containing the elimination rate constant Examples AUMC0_inf(A12,A13,A14,A15) 3. How to enter the PK Functions Enter the functions in the same way as you normal enter functions like AVERAGE. Either type them in directly or else choose Insert...Function from the menu bar. The easiest way to place the functions into the spreadsheet is to choose the Function... item from the Insert menu. Select User-Defined in the left-hand Function Category selection box, and select the PK function you need from the right-hand Function Name box. A dialog screen will prompt for the ranges to be used. Either type in the ranges, or more simply, just point to the appropriate ranges in the spreadsheet with your mouse. 4. Error-Checking Capabilities Datasets, in pharmacokinetics, are naturally constrained . For example, concentrations have to be non-negative, and time values need to be in ascending order. These PK Functions perform basic error-checking in the data entry ranges (see Appendix B) and will respond with an error message if errors in the datasets are found. 5. Validation These functions were originally designed and developed for Microsoft Excel for the Macintosh by Joel Usansky and Atul Desai of the Department of Pharmacokinetics and Drug Metabolism at Allergan, Irvine CA 92612. They have since been successfully transferred to Microsoft Excel for Windows. The Macintosh and Windows versions PK Functions have been successfully validated according to computer system development life cycle standards and GLP guidelines. 6. Tips for Use Using combinations of absolute and relative references can simplify repeated calculations on different subjects. For example, suppose you have time data in Column A and concentration data for subjects 1 to 12 in Columns B through M. For subject no. 1, enter the formula: =AUC0_t(B3:B10,$A3:$A10) into column B. Note the dollar symbols which lock onto the time data column. Then simply copy the formula to the right and all the formulas will specify the correct time and concentration ranges you need. The AUC0_inf and AUMC0_inf functions always calculate the correct values when the function is first entered into the Excel spreadsheet. The AUC0_inf and AUMC0-inf functions use the output from the AUC0_t and AUMC0-t functions, and if the time or concentration data in the AUC0_t and AUMC0-t functions are subsequently altered, then the AUC0_inf and AUMC0-inf functions do not automatically update. It is then necessary to recalculate the Excel spreadsheet (Press function key F9 or choose Options from the Tools menu and click the Calculate Now button on the Calculation tab). 7. References Gibaldi M and Perrier M. Pharmacokinetics. 2nd Edition. Marcel Dekker. New York. 1982. Rowland M and Tozer T. Clinical Pharmacokinetics. Concepts and Applications. 3rd Edition. Lea and Febiger. 1997. Wagner JG. Pharmacokinetics for the Pharmaceutical Scientist. Technomic Publishing Co. 1993. 8. Questions or Comments Dr. Joel I. Usansky Department of Pharmacokinetics and Drug Metabolism Allergan 2525 Dupont Drive Irvine, CA 92612 Phone: (714) 246-4339 Fax: (714) 246-5850 Email: usansky_joel@allergan.com Disclaimer The PK Functions are provided, without cost, for non-profit research use only. By using this software, you expressly acknowledge and agree that such use is at your sole risk and is your sole responsibility. The PK Functions are provided AS IS. JOEL USANSKY, ATUL DESAI, DIANE TANG-LIU AND ALLERGAN HEREBY DISCLAIM ANY WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. The authors make no representation that the PK Functions will meet your requirements or that operation of the PK Functions will be error-free or uninterrupted. Additionally, by using the PK Functions you agree that Joel Usansky, Atul Desai, Diane Tang-Liu and Allergan shall not be responsible for loss of data, or liable for damages of any kind, including without limitation, actual, general, special, consequential or incidental damages, or for injury of any kind to person or property, or for infringement of the intellectual property rights of any entity, that may arise from or in connection with your use of the PK Functions. The PK Functions are written for use with Microsoft Excel, a software product owned and licensed by Microsoft Corp. Users of PK Functions must have a valid license to use Microsoft Excel, and Joel Usansky, Atul Desai, Diane Tang-Liu and Allergan hereby condition the right to use PK Functions to such valid licensees of Microsoft Excel. APPENDIX A Definition of the PK Functions 1) Area under a curve from time = 0 to time t [AUC0_t] Calculates area under the curve for concentration vs. time data using linear trapezoidal rule (1). The use of the linear trapezoidal rule as a method for approximating the area under a concentration-time curve is widely accepted. The accuracy of the approximation to true area under a curve depends on the number of concentration-time points within the time interval under consideration. The formula to calculate the area under the curve is as follows: AUC0_t =  EQ \I\SU ( i=0,n-1,(ti+1 - ti)) * (Ci + Ci+1) / 2 Where, AUC0_t = Area under a curve for time 0 to t t = time values C = Concentration values, e.g. Ci at time ti n = Total nos. of time concentration points i = Reference index for ith concentration-time value. 2) Area under a moment curve from time = 0 to time t [AUMC0_t] Calculates area under the curve for (concentration * time) vs. time data using the linear trapezoidal rule (1). The use of a linear trapezoidal rule as a method for approximating the area under a moment curve is widely accepted. The accuracy of the approximation to true area under a moment curve depends on the number of concentration-time points within the time interval under consideration. The formula to calculate the area under the moment curve is as follows: AUMC0_t =  EQ \I\SU (i=0,n-1,(ti+1 * Ci+1 + ti * Ci)) * (ti+1 - ti) / 2 Where, AUMC0_t = Area under a moment curve for time 0 to t t = time values C = Concentration values, e.g. Ci at time ti n = Total nos. of concentration-time points i = Reference index for ith sample value. 3) 3) 3Elimination rate constant [ElimRateConst] The elimination rate constant is approximated by the average of instantaneous rates over a finite period of time. The value is calculated as the negative slope of the non-weighted least squares curve fit for the logarithmically transformed concentration data versus time data (1). The formula to calculate the elimination rate constant is as follows: ln Ct = ln b - t * ElimRateConst Where, Ct = Concentration at time t b = Constant ElimRateConst = Elimination rate constant ln = Natural logarithm 4) Half-life [Half_Life] Half-life is defined as a time at which concentration reaches 50% of its initial value. The formula to calculate the half-life is as follows: t1/2 = ln(2) / ElimRateConst Where, t1/2 = Half-life ElimRateConst = Elimination rate constant ln = Natural logarithm 5) Area under a curve from time = 0 to time infinity [AUC0_inf] Calculates area under the curve from concentration vs. time data for time zero to infinity (1). This estimation of area under the curve is carried out in two steps. The area under the curve from time zero to tlast (time of last measurable concentration, Clast) is calculated by means of a linear trapezoidal rule. The area under the curve from time tlast to infinity is estimated by assuming log-linear decline. The two areas described above are summated to yield AUC0_inf. The formula to calculate the area under the curve is as follows: AUC0_inf = AUC0_t + Clast / ElimRateConstant Where, AUC0_inf = Area under a curve for time 0 to infinity. ElimRateConstant = Elimination rate constant AUC0_t = Area under a curve for time 0 to t Clast = The last measurable concentration at time tlast 6) Area under a moment curve from time = 0 to time infinity [AUMC0_inf] Calculates area under the curve for (concentration * time) vs. time data for time zero to infinity (1). This estimation of area under the curve is carried out in two steps. The area under the curve from time zero to tlast (time of last measurable concentration, Clast) is calculated by means of a linear trapezoidal rule. The area under the curve from time tlast to infinity is estimated using statistical moment theory. The two areas described above are summated to yield AUMC0_inf. The formula to calculate the area under the moment curve is as follows: AUMC0_inf = AUMC0_time + (tlast * Clast / ElimRateConstant ) + (Clast / ElimRateConstant2) Where, AUMC0_inf = Area under a moment curve for time 0 to infinity. ElimRateConst = Elimination rate constant AUMC0_time = Area under a moment curve for time 0 to t Clast = The last measurable concentration at time tlast tlast = The time of the last measurable concentration APPENDIX B Error-Checking Capabilities of the PK Functions 1) Area under a curve from time = 0 to time t [AUC0_t] There should be only one column of time data. The functions should check for data errors and provide message(s) if; 1) Nos. of rows of concentration values and the time values are not equal. 2) The time value points are not in ascending order. 3) Nos. of rows of time and concentration values are less than 2; i.e., only one value specified in the range. 4) The time or concentration ranges specified are more than one column. 5) Invalid data in the data range, e.g. a) Negative values of concentration or time data b) Non-numerical character in the concentration or time data c) The concentration or the time values are missing, i.e. blank row or invalid value in the data range. 2) Area under a moment curve from time = 0 to time t [AUMC0_t] There should be only one column of concentration data. There should be only one column of time data. The functions should check for data errors and provide message(s) if; 1) Nos. of rows of concentration values and the time values are not equal. 2) The time value points are not in ascending order. 3) Nos. of rows of time and concentration values are less than 2; i.e., only one value specified in the range. 4) The time or concentration ranges specified are more than one column. 5) Invalid data in the data range, e.g. a) Negative values of concentration or time data b) Non-numerical character in the concentration or time data c) The concentration or the time values are missing, i.e. blank row or invalid value in the data range. 3) 3) 3Elimination rate constant [ElimRateConst] There should be only one column of concentration data. There should be only one column of time data. The functions should check for data errors and provide message(s) if; 1) Nos. of rows of concentration values and the time values are not equal. 2) The time value points are not in ascending order. 3) Nos. of rows of time and concentration values are less than 2; i.e., only one value specified in the range. 4) The time or concentration ranges specified are more than one column. 5) Invalid data in the data range, e.g. a) Negative values of concentration or time data b) Non-numerical character in the concentration or time data c) The concentration or the time values are missing, i.e. blank row or invalid value in the data range. Half-life There should be only one column of concentration data. There should be only one column of time data. The functions should check for data errors and provide message(s) if; 1) Nos. of rows of concentration values and the time values are not equal. 2) The time value points are not in ascending order. 3) Nos. of rows of time and concentration values are less than 2; i.e., only one value specified in the range. 4) The time or concentration ranges specified are more than one column. 5) Invalid data in the data range, e.g. a) Negative values of concentration or time data b) Non-numerical character in the concentration or time data c) The concentration or the time values are missing, i.e. blank row or invalid value in the data range. Area under a curve (Time = 0 to infinity) There should be a value for area under a curve (AUC0_time) for time 0 to t. There should be a value for last measurable concentration at a time t. There should be a value for Elimination Rate Constant (ElimRateConstant). The functions should check for data errors and provide message(s) if; 1) The concentration, AUC0_time or ElimRateConstant values are missing 2) The concentration, AUC0_time or ElimRateConstant values are negative. 3) Non-numerical character in the concentration, AUC0_time or ElimRateConstant value. Area under a moment curve (Time = 0 to infinity) There should be a value for area under a moment curve (AUMC0_time) for time 0 to t There should be a value for last measurable concentration at a time t. There should be a value for time of last measurable concentration. There should be a value for Elimination Rate Constant (ElimRateConstant). The functions should check for data errors and provide message(s) if; 1) The time, concentration, AUMC0_time or ElimRateConstant value is missing 2) The time, concentration, AUMC0_time or ElimRateConstant value is negative. 3) Non-numerical character in the time, concentration, AUMC0_time or ElimRateConstant value !? b  x NP09 ,5ɶyyyh;>*B*phh;5B*OJQJphh;5B*phh^d/B*phh,h,B*phhP B*phh^d/h^d/B*phh,B*phh^d/h^d/>*B*phh^d/>*B*phh;B*OJQJphh;5>*B*phh;B*ph/!"efk l > ? a b  w x gd^d/$ a$'(0 #$, p^p`^^!!IR9CDjqaj,3kt"  AJ /<=th;5B*OJQJphh;B*OJQJphh;5B*phh;>*B*phh;B*phO!@AI78 p^p`^^89Dijq`aj+,3bck p^p`^^ 89A  -./= p^p`^^tp!q!!!""""$$$$%%%%^ p^p`t  " ( q!!""$$))K*d*%+0+//102000000(1512333"3&3͎zh"h;5@B*EHOJQJph'jh;5@B*OJQJUphh;5@B*OJQJphh;5B*OJQJphh;5>*B*OJQJph"h;@B*CJH*OJQJphh;B*OJQJphh;5>*B*phh;>*B*phh;B*phh;5B*ph(%&&)))))r)s)))J*K*d*e*y*******+$+%+0+1+t/ & F t/u/00000005122>3B3z33335464744T6U66 @ L]L @  p  $a$&3'3+3,303135383>333334474m44T6U6_6`6w6z6~6666666666666 7$7-7/7}7777޾w޾h;@B*OJQJphh;5B*OJQJphh;5>*B*OJQJphh;B*EHOJQJphh;B*EHOJQJphh;B*OJQJph'jh;5@B*OJQJUphh;5@B*OJQJph"h;5@B*EHOJQJph,6666/7_77777,909S9U9{9999999:::::: ; ;  7777296989U9^9a99999999::::::::: ; ;B;V;(<)<.<W<X<\<<<<z====O>T>>>>> ????????B@C@H@ AҶƨҶƨҶҶҶҶƨƨƨҶҶҶh;>*B*OJQJphh;B*EHOJQJphh;5B*EHOJQJphh;B*OJQJphh;5B*OJQJphh;5>*B*OJQJph h;5<>*B*OJQJph< ; ;V;x=y=z====>L>>>>> A AJAiAjAAABQBB p ^]L]L p  p  A(A,A0A4AOASAfAgAhAiAjABBLBQBRBWBBBBBBBBCEEEHHHHHIKLNOQOQSS̾q h;5<>*B*OJQJphh;5>*B*OJQJphh;5>*B*phh;B*phh;B*EHOJQJphh;B*OJQJphh;5B*OJQJph"h;5@B*EHOJQJph"h;5@B*EHOJQJphh;5@B*OJQJph*BBBBBBBC0CvCCCeDDDEEEEEE.F\FFF"GGGH @ L]L$a$H3HqHHHILIzII J@JJJKQKKKKL:LhLLL.MMM N?N}NNNNO]OOO4P|PPQQOQQQ,RvRR SXSSS(/ =!"#$% f 666666666vvvvvvvvv666666>6666666666666666666666666666666666666666666666666hH6666666666666666666666666666666666666666666666666666666666666666662 0@P`p2( 0@P`p 0@P`p 0@P`p 0@P`p 0@P`p 0@P`p8XV~OJQJ_HmH nH sH tH <`< NormalCJ_HmH sH tH DA`D Default Paragraph FontViV 0 Table Normal :V 44 la (k ( 0No List :>@: Title$ a$5>*2B@2  Body TextB*PK![Content_Types].xmlj0Eжr(΢Iw},-j4 wP-t#bΙ{UTU^hd}㨫)*1P' ^W0)T9<l#$yi};~@(Hu* Dנz/0ǰ $ X3aZ,D0j~3߶b~i>3\`?/[G\!-Rk.sԻ..a濭?PK!֧6 _rels/.relsj0 }Q%v/C/}(h"O = C?hv=Ʌ%[xp{۵_Pѣ<1H0ORBdJE4b$q_6LR7`0̞O,En7Lib/SeеPK!kytheme/theme/themeManager.xml M @}w7c(EbˮCAǠҟ7՛K Y, e.|,H,lxɴIsQ}#Ր ֵ+!,^$j=GW)E+& 8PK!Ptheme/theme/theme1.xmlYOo6w toc'vuر-MniP@I}úama[إ4:lЯGRX^6؊>$ !)O^rC$y@/yH*񄴽)޵߻UDb`}"qۋJחX^)I`nEp)liV[]1M<OP6r=zgbIguSebORD۫qu gZo~ٺlAplxpT0+[}`jzAV2Fi@qv֬5\|ʜ̭NleXdsjcs7f W+Ն7`g ȘJj|h(KD- dXiJ؇(x$( :;˹! I_TS 1?E??ZBΪmU/?~xY'y5g&΋/ɋ>GMGeD3Vq%'#q$8K)fw9:ĵ x}rxwr:\TZaG*y8IjbRc|XŻǿI u3KGnD1NIBs RuK>V.EL+M2#'fi ~V vl{u8zH *:(W☕ ~JTe\O*tHGHY}KNP*ݾ˦TѼ9/#A7qZ$*c?qUnwN%Oi4 =3ڗP 1Pm \\9Mؓ2aD];Yt\[x]}Wr|]g- eW )6-rCSj id DЇAΜIqbJ#x꺃 6k#ASh&ʌt(Q%p%m&]caSl=X\P1Mh9MVdDAaVB[݈fJíP|8 քAV^f Hn- "d>znNJ ة>b&2vKyϼD:,AGm\nziÙ.uχYC6OMf3or$5NHT[XF64T,ќM0E)`#5XY`פ;%1U٥m;R>QD DcpU'&LE/pm%]8firS4d 7y\`JnίI R3U~7+׸#m qBiDi*L69mY&iHE=(K&N!V.KeLDĕ{D vEꦚdeNƟe(MN9ߜR6&3(a/DUz<{ˊYȳV)9Z[4^n5!J?Q3eBoCM m<.vpIYfZY_p[=al-Y}Nc͙ŋ4vfavl'SA8|*u{-ߟ0%M07%<ҍPK! ѐ'theme/theme/_rels/themeManager.xml.relsM 0wooӺ&݈Э5 6?$Q ,.aic21h:qm@RN;d`o7gK(M&$R(.1r'JЊT8V"AȻHu}|$b{P8g/]QAsم(#L[PK-![Content_Types].xmlPK-!֧6 +_rels/.relsPK-!kytheme/theme/themeManager.xmlPK-!Ptheme/theme/theme1.xmlPK-! ѐ' theme/theme/_rels/themeManager.xml.relsPK] K zt&37 AS*-25798%t/6 ;BHNS+,./013468:;<+++_..K118@0(  B S  ?FJbgw%09 ! , 5    ! ! * + > I R  % x"#6AJtY!_!s!z!!!!!!!*"3"'',(2(((0+1+++++++,,.. /!/-/./`/a/|////E1R111112222555579G9V9f999AAGGXHhHHHIIcJsJJJ4KDKKKKwKKccmT @0^`0.ccmP ,^d/e0;KK@x.&&KP@UnknownG* Times New Roman5Symbol3. * ArialMCentury Schoolbook3* TimesACambria Math"1h$$6&L j@ &L j@ &!0dKK3HX $Pe02!xxHave you ever wished that Microsoft Excel had in-built pharmacokinetic functions just like SUM, AVERAGE and STDEV? Well now it has! Allergan joel.usansky Oh+'0(4HTh     Have you ever wished that Microsoft Excel had in-built pharmacokinetic functions just like SUM, AVERAGE and STDEV? Well now it has!  Allergan Normal.dotmjoel.usansky5Microsoft Office Word@@J@&U1@g1 L j@՜.+,0| hp  [Allergan, Inc.&K Have you ever wished that Microsoft Excel had in-built pharmacokinetic functions just like SUM, AVERAGE and STDEV? Well now it has! Title  !"#$%&'()*+,-./0123456789:;<=?@ABCDEFGHIJKLMOPQRSTUWXYZ[\]`Root Entry F1b1Table>WordDocument*zSummaryInformation(NDocumentSummaryInformation8VCompObjy  F'Microsoft Office Word 97-2003 Document MSWordDocWord.Document.89q