resources from my career
In this post I will provide
If you are interested in downloading the template, e-mail me here.
Click "Read More" below and over to the right...
Reference #1: Percent of Days in Range (Rosendaal Method)
“This is the most complex of the calculations, as it looks at the amount of time between visits to determine how long the patient might have been within their therapeutic range. If a patient has a therapeutic range of 2.0 - 3.0, and on May 1st tested at 2.5, and then tested 3.5 on May 31st, then we can determine how many days were in range. Since there were 30 days between tests, you assume that the patient slowly moved from 2.5 to 3.5 over those 30 days, so around May 15th, the patient was probably over 3.0, and therefore was out of range. Therefore, we estimate that 15 days were in range, and 15 days were out of range (within the 30 day time period), which means the patient is within range 50% of the time.”
Reference #2: Warfarin Time in Therapeutic Range - Supplemental SQL Logic Reference
“Warfarin time in therapeutic range is the percent of time in which patients with atrial fibrillation or flutter who are on chronic Warfarin therapy have INR test results within the therapeutic range (2.0 - 3.0) during the measurement period.
“The following filters are applied to the INR results prior to the calculation of TTR for each patient:
1) INR value closest to 2.5 when there is more than one INR result on a single date
2) INR values greater than 10 will be replaced with an INR value of 10
3) INR values less than 0.8 are ignored and eliminated from the final TTR calculation for each patient
“The logic keeps track of the number of valid INR intervals for each patient. A Valid INR Interval is defined as a pair of INR start dates that are less than or equal to 56 days apart. Patients without 2 such intervals will be excluded from the calculation of the providers’ Average PctTTR later on.”
TTR Calculation Steps
There are 11 steps with Excel formulas and print screens. The print-screen image of each step is below the step text.
Step 1: Pull patient INR results using a query similar to the one below. Your query should
Other criteria for a successful TTR calculation will be addressed in the Excel steps.
Step 2: Paste results into Excel
Step 3: Create column for number of days between results
Step 4: Create Column “A” for the “High INR value” and Column “B” for the “Low INR Value”
Step 5: Create Column “(A-B)/D” for the total change between the INR results divided by the number of days between the INR results is the “Change per Day.”
Step 6: Create Columns for “Days out of Range on Low End” and “Days out of Range on High End” in columns L and M
Step 7: Insert formulas for calculation
1. For Days out of Range on Low End (in column L): =IFERROR(IF(AND(K4=0,E4<2),H4,(2-J4)/K4),0)
2. For Days out of Range on High End (in column M): =IFERROR(IF(AND(K4=0,E4>3),H4,((I4-3)/K4)),0)
3. Drag down formulas
Step 8: Create column for Days in Therapeutic Range
Most of the calculations so far have been done in the second record because the first record does not have a previous value.
The next calculations aggregate the TTR for each patient. They will be done in the first record and, using “IF” statements, they will be repeated when the value in column G (rank) is equal to 1 (once per patient).
Step 9: Create column for Patient Days TTR (Number of days for each patient in therapeutic range)
Step 10: Create column for Patient TTR % (Percentage of days for each patient in therapeutic range)
Step 11: Clean up the data