Geometric progression in Excel [Design Issues]

posted by Helmut Homepage – Vienna, Austria, 2013-02-28 14:56 (4071 d 23:23 ago) – Posting: # 10131
Views: 9,251

Hi intuitivepharma!

❝ I sincerely apologise for reopening an old discussion thread.


No problem.

❝ Formula I used was,

Ti = (B4-1)*((B2/B1)^(1/(B3-1)))


First Time Point [B1]    0.0833

Last Time Point [B2]     12

No of time points [B3]   15

Index of time point [B4] 15


Let’s see. First sampling at 5 min, last at 12 h, 15/16/17 sampling points. Enter formulas in cells A4:A5 and drag to the others.
                A                         B                         C             
   1 =5/60                      =$A1                       =$A1
   2 12                         12                         12
   3 15                         16                         17
   4 =A$1                       =B$1                       =C$1
   5 =A4*(A$2/A$1)^(1/(A$3-1))  =B4*(B$2/B$1)^(1/(B$3-1))  =C4*(C$2/C$1)^(1/(C$3-1))
   6 =A5*(A$2/A$1)^(1/(A$3-1))  =B5*(B$2/B$1)^(1/(B$3-1))  =C5*(C$2/C$1)^(1/(C$3-1))
   7 =A6*(A$2/A$1)^(1/(A$3-1))  =B6*(B$2/B$1)^(1/(B$3-1))  =C6*(C$2/C$1)^(1/(C$3-1))
   8 =A7*(A$2/A$1)^(1/(A$3-1))  =B7*(B$2/B$1)^(1/(B$3-1))  =C7*(C$2/C$1)^(1/(C$3-1))
   9 =A8*(A$2/A$1)^(1/(A$3-1))  =B8*(B$2/B$1)^(1/(B$3-1))  =C8*(C$2/C$1)^(1/(C$3-1))
  10 =A9*(A$2/A$1)^(1/(A$3-1))  =B9*(B$2/B$1)^(1/(B$3-1))  =C9*(C$2/C$1)^(1/(C$3-1))
  11 =A10*(A$2/A$1)^(1/(A$3-1)) =B10*(B$2/B$1)^(1/(B$3-1)) =C10*(C$2/C$1)^(1/(C$3-1))
  12 =A11*(A$2/A$1)^(1/(A$3-1)) =B11*(B$2/B$1)^(1/(B$3-1)) =C11*(C$2/C$1)^(1/(C$3-1))
  13 =A12*(A$2/A$1)^(1/(A$3-1)) =B12*(B$2/B$1)^(1/(B$3-1)) =C12*(C$2/C$1)^(1/(C$3-1))
  14 =A13*(A$2/A$1)^(1/(A$3-1)) =B13*(B$2/B$1)^(1/(B$3-1)) =C13*(C$2/C$1)^(1/(C$3-1))
  15 =A14*(A$2/A$1)^(1/(A$3-1)) =B14*(B$2/B$1)^(1/(B$3-1)) =C14*(C$2/C$1)^(1/(C$3-1))
  16 =A15*(A$2/A$1)^(1/(A$3-1)) =B15*(B$2/B$1)^(1/(B$3-1)) =C15*(C$2/C$1)^(1/(C$3-1))
  17 =A16*(A$2/A$1)^(1/(A$3-1)) =B16*(B$2/B$1)^(1/(B$3-1)) =C16*(C$2/C$1)^(1/(C$3-1))
  18 =A17*(A$2/A$1)^(1/(A$3-1)) =B17*(B$2/B$1)^(1/(B$3-1)) =C17*(C$2/C$1)^(1/(C$3-1))
  19                            =B18*(B$2/B$1)^(1/(B$3-1)) =C18*(C$2/C$1)^(1/(C$3-1))
  20                                                       =C19*(C$2/C$1)^(1/(C$3-1))


You should get:
0.0833  0.0833  0.0833
    12      12      12
    15      16      17
0.0833  0.0833  0.0833
0.1188  0.1161  0.1137
0.1695  0.1617  0.1551
0.2417  0.2252  0.2116
0.3447  0.3136  0.2887
0.4917  0.4368  0.3938
0.7012  0.6084  0.5373
1.0000  0.8473  0.7330
1.4262  1.1802  1.0000
2.0339  1.6438  1.3643
2.9007  2.2894  1.8612
4.1369  3.1887  2.5392
5.8999  4.4413  3.4641
8.4142  6.1859  4.7259
12.000  8.6157  6.4474
        12.000  8.7960
                12.000


❝ I understand if I had coded the algorithm correctly, then when I key in 15 at the index of time point, I should get 12 hrs as the time point. I got 19.966 instead. When I key in 1 at the index of time point, I should get 0.0833 hrs as the time point. I got 0 instead.


No idea about indexing in M$ Excel. My coding is simple.

❝ As indicated by you the above formula will suffice the purpose of spacing time points as per a geometric progression and the output needs to be fine tuned towards practicability and based on individual formulation characteristic.


Yes. Also keep variability in tmax across subjects in mind. I would use evenly spaced sampling until the expected tmax and use the geometric progression only afterwards. Therefore, t1 = tmax.

❝ On a more scientific basis, can I derive any input from the “partial derivatives plot” generated by WinNonlin for sampling point determination? The WinNonlin write-up on the same is not very comprehensive. Kindly throw some light on the same.


The extremes (min/max) of the partial derivatives show the ‘most influentual’ time points – if you are interested in PK modeling. For BE you can only use K10 (NCA-speak: λz). In your example 36 hours is most important and the interval 16–72 ‘interesting’. But that’s trivial, isn’t it?

❝ Based on the partial derivatives plot [with information on Ka, Kel and VD] can you please guide how to optimise the sampling time points?


❝ The idea is to generate the final time points based on geometric progression and fine tuning it with the inputs from partial derivatives plot.


I’m running a large PopPK-model in Phoenix right now – maybe I will post some examples later on. However, I don’t think that it will be really useful.

Dif-tor heh smusma 🖖🏼 Довге життя Україна! [image]
Helmut Schütz
[image]

The quality of responses received is directly proportional to the quality of the question asked. 🚮
Science Quotes

Complete thread:

UA Flag
Activity
 Admin contact
22,990 posts in 4,826 threads, 1,647 registered users;
49 visitors (1 registered, 48 guests [including 2 identified bots]).
Forum time: 15:19 CEST (Europe/Vienna)

If you don’t like something change it;
if you can’t change it, change the way you think about it.    Mary Engelbreit

The Bioequivalence and Bioavailability Forum is hosted by
BEBAC Ing. Helmut Schütz
HTML5