Pharmacokinetics in Excel - how to account for multiple drug intakes?

Question

As painful as it sounds, I've been tasked to implement a spreadsheet to calculate drug absorption and elimination half-life. I actually use LibreOffice Calc, but a solution tested in Excel would also help me (as it'll probably be portable, anyway).

My spreadsheet currently looks like this:

It must account for multiple dose intakes (i.e. arbitrary new entries in column D). As an example, the person who's going to use this spreadsheet needs to take a new 50mg dose of the drug every 56 hours.

In order to write a working formula on column C, I followed many steps:
(all examples are for C3; C2 is manually set to 0)

1. = ( C2 * 0,5 ^ ( (B3 - B2) / $H$3 ) ) + D2 * J$2 - this does take into consideration multiple dose intakes, but does not account for absorption time. Instead, it returns 50 on C3 (after 8 hours), 48.577 on C4 (16 hours), (...) and 25.000 on C27 (192 hours, or 1 half-life), etc. In other words, the drug concentration on the blood rises immediatelly. Instead, it should rise linearly, according to 'Time to C_max' in H2.

2. = IFERROR( ( ( INDEX( $D$2:$D2; MATCH(1E+306; $D$2:$D2; 1) ) * J$2 ) * MIN( ( B3 - ( INDEX( $B$2:$B2; MATCH(1E+306; $D$2:$D2; 1) ) ) ) / $H$2; 1 ) ); 0 ) - this calculates the total absorption over time, it rises linearly until reaching 50 in C16 (112 hours) and stays there afterwards. It is different than the previous formula, as this one finds the last value entered on column D (with a combination of INDEX and MATCH). But, because of that, it no longer accounts for multiple intakes - only the last one.

3. = IFERROR( ( ( ( INDEX( $D$2:$D2; MATCH(1E+306; $D$2:$D2; 1) ) * J$2 ) * MIN( ( B3 - ( INDEX( $B$2:$B2; MATCH(1E+306; $D$2:$D2; 1) ) ) ) / $H$2; 1 ) ) - ( ( INDEX( $D$2:$D2; MATCH(1E+306; $D$2:$D2; 1) ) * J$2 ) * MIN( ( B2 - ( INDEX( $B$2:$B2; MATCH(1E+306; $D$2:$D2; 1) ) ) ) / $H$2; 1 ) ) ); 0 ) - this returns the amount of the drug that was absorbed over the last 8-hour (1 row) period. I.e., C3 is 3.704, C4 is also 3.704, (...) C15 (104 hours) is also 3.704, while C16 (112 hours) is 1.852 and C17 and below are 0 (assuming there is no other dose intake other than that in D2). It has the same shortcoming as the previous formula.

4. = ( C2 * 0,5 ^ ( (B3 - B2) / $H$3 ) ) + IFERROR( ( ( ( INDEX( $D$2:$D2; MATCH(1E+306; $D$2:$D2; 1) ) * J$2 ) * MIN( ( B3 - ( INDEX( $B$2:$B2; MATCH(1E+306; $D$2:$D2; 1) ) ) ) / $H$2; 1 ) ) - ( ( INDEX( $D$2:$D2; MATCH(1E+306; $D$2:$D2; 1) ) * J$2 ) * MIN( ( B2 - ( INDEX( $B$2:$B2; MATCH(1E+306; $D$2:$D2; 1) ) ) ) / $H$2; 1 ) ) ); 0 ) - this is was my current state-of-the-art formula. It accounts for both the linear absorption and the logarithmic elimination of the amount absorbed.

5. 2019-10-11 update: I came up with a performance improvement to step 3 above, in the form of: = IFERROR( ( ( IF( ( B3 - INDEX( $B$2:$B2; MATCH(1E+306; $D$2:$D2; 1) ) ) <= H$2; (B3 - B2); IF( ( ( B3 - INDEX( $B$2:$B2; MATCH(1E+306; $D$2:$D2; 1) ) ) - H$2 ) < (B3 - B2); ( ( B3 - INDEX( $B$2:$B2; MATCH(1E+306; $D$2:$D2; 1) ) ) - H$2 ); 0 ) ) * INDEX( $D$2:$D2; MATCH(1E+306; $D$2:$D2; 1) ) ) / H$2 ); 0 ).

   The updated, complete formula and current state-of-the art, as seen in the screenshot above, is, then: = ( C2 * 0,5 ^ ( (B3 - B2) / $H$3 ) ) + IFERROR( ( ( IF( ( B3 - INDEX( $B$2:$B2; MATCH(1E+306; $D$2:$D2; 1) ) ) <= H$2; (B3 - B2); IF( ( ( B3 - INDEX( $B$2:$B2; MATCH(1E+306; $D$2:$D2; 1) ) ) - H$2 ) < (B3 - B2); ( ( B3 - INDEX( $B$2:$B2; MATCH(1E+306; $D$2:$D2; 1) ) ) - H$2 ); 0 ) ) * INDEX( $D$2:$D2; MATCH(1E+306; $D$2:$D2; 1) ) ) / H$2 ); 0 ).

However, this formula still does not account for subsequent dose intakes, not correctly anyway. I.e. if I type in a new value of 50 in cell D9, the value in cell C10 should increase, but it does not.
After giving it much thought and looking for help elsewhere, I still don't really know how it can be made so.

Can anyone help?

---

As additional context, the drug in this example is testosterone cypionate (TC), is administered as intramuscular injection, and 'T / TC ratio' refers to the ratio of testosterone (T) that is present on TC, and I don't really know if the ratio is actually 1. 'Time to C_max' is the amount of time it takes for the drug to reach maximum concentration on the blood.

I was told the absorption might not be entirely linear, and the drug might not be 100% absorbed when C_max is reached, meaning some of it will continue to be absorbed after that; nevertheless, I'm allowed to assume it is linear and 100% absorbed at C_max, as that is a good enough approximation for the purposes of the person who is going to use the spreadsheet.

I'm putting the spreadsheet on Google Drive to make it easier for others to assist me.

Thanks.

Answer 1

Try this one:

First of all, as there is no way to declare a possible neverending funtion, just make the calculation for 1 mg until the concentration get extinguish.

Keeping that one in one column (I place in column J), you just need to make a loop.

Public Function Concent(Time_Origin As Long)
    Dim Cycle As Long
    Dim i As Long
    Dim j As Long
    Dim Items(10000) As Long
    Dim Intake(10000) As Long
    Dim Concent_1mg(2152) As Double
    
    j = 0
    Cycle = Time_Origin / 8 + 7
    
    For i = 6 To Cycle
        If Cells(i, 4) <> "" Then
            Items(j + 1) = i
            Intake(j + 1) = Cells(i, 4).Value2
            j = j + 1
        End If
    Next
    
    For i = 1 To 2147
        Concent_1mg(i) = Cells(i + 5, 10).Value2
    Next
    
    For i = 1 To j
        Concent = Concent + Intake(i) * Concent_1mg(Cycle - Items(i))
    Next i
End Function

Once this, it is just a matter to call this function that will calculate the concentration with the time since origin.

Hope it helps