Geometric progression in Excel [Design Issues]
❝ 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 🖖🏼 Довге життя Україна!
Helmut Schütz
The quality of responses received is directly proportional to the quality of the question asked. 🚮
Science Quotes
Complete thread:
- How to Define Sampling Interval eduardojmorales 2006-10-05 21:11 [Design Issues]
- How to Define Sampling Interval eduardojmorales 2006-10-05 23:16
- Soliloquy? H_Rotter 2006-10-06 13:27
- Soliloquy? eduardojmorales 2006-10-10 18:09
- Algorithm (geometric progression) Helmut 2006-10-07 21:48
- Algorithm (geometric progression) eduardojmorales 2006-10-10 18:23
- Algorithm (geometric progression) Helmut 2006-10-10 19:05
- Algorithm (geometric progression) intuitivepharma 2013-02-28 08:10
- Geometric progression in ExcelHelmut 2013-02-28 13:56
- Sampling (optimization?) Helmut 2013-02-28 16:50
- Sampling (optimization?) intuitivepharma 2013-03-01 07:42
- Sampling (optimization?) SDavis 2013-03-07 10:40
- VIFs? Helmut 2013-03-07 15:50
- Sampling (optimization?) intuitivepharma 2013-03-08 12:39
- Sampling (optimization?) SDavis 2013-03-07 10:40
- Sampling (optimization?) intuitivepharma 2013-03-01 07:42
- Algorithm (geometric progression) eduardojmorales 2006-10-10 18:23
- Soliloquy? H_Rotter 2006-10-06 13:27
- How to Define Sampling Interval eduardojmorales 2006-10-05 23:16