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
)
= ( 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 Cmax' in H2.
= 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.
= 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.
= ( 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.
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 Cmax' 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 Cmax 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 Cmax, 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.
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.