Shahriar Abrar Rafid
Have you ever heard of the term median follow-up time? Most people, especially those who don’t have involvement in the technical sector haven’t heard of it. But it’s a very well-known perception among doctors and medical researchers and scientists. In most cases, they use it for oncology. In this article, we’ll get to learn about this. Also, we’ll show 3 easy, quick, and understandable steps to calculate median follow-up time in Excel. So, let’s go through the article step by step.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
What Is Median Follow-up Time?
Median is a statistical term. In statistics and probability, it indicates the value distinguishing the upper half portion from the underside half portion of a population, a probability distribution, or a set of information. But, what is the median follow-up time? Basically, it’s an immensely popular and commonly used term in medical research and studies. It is the median of time that passes between a specific occurrence and the moment that outcome data is collected. In assessments of cancer survival, the idea is applied. A lot of cancer research focuses on measuring the interval between two notable events, such as the time between therapy and remission, treatment and relapse, or diagnosis and death. Even if death is not the end result, this time period is generally referred to as survival time.
3 Steps to Calculate Median Follow-up Time/Duration in Excel
For ease of understanding, we’re using a Follow-up History of Cancer Patient. In this imaginary study, there are 10 patients. This dataset includes the serial number of Patient, the Date of Admission, Date of Last Follow-up, Status at Last Follow-up and End Date of the Study in columns B, C, D, E, and F respectively.
All but one thing is to be understood here. What’s the role of 1 and 0 in the Status at Last Follow-up? Any clue? Actually, it’s the status of the patient at the time of last follow-up. It means that is he alive or dead on that day. 1 means dead and 0 means still alive. Now, we’ll calculate the median follow-up time of this study.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.
Step 01: Determine Status at End Date
In this first step, we’ll determine the status of the patient at the end date of the study. Let’s explore this step by step.
📌 Steps:
- At the very beginning, create a new column with the heading Status at End Date under Column G.
Now, we’ve to enter the end status of patient 1 in cell G5. But how can we determine that? Any ideas? If the answer is no, then pay attention to the following paragraph.
There is the information for Patient 1 in Row 5. Here, we can see that he took admission on 5 Jan 2021 and his last follow-up date was 5 Oct 2021. And at that date, he was already dead (there is 1 in cell E5). As a result, by the end of this study, on June 30, 2022, his status should also be dead. Because the end date is a date after the last follow-up.
- So, go to cell G5 and write down 1 (as it means dead).
Note: If you find it difficult to understand, read it a few times and match it with the above image. I think it will be easy then.
For the second patient, his admission date is 2 Feb 2021 and the last follow-up date is 12 Jul 2022 which is beyond the end date of the study. And at that time, he was dead. So, at the end date of the study, he was alive. because his day of death is 12 Jul 2022 which is a few days after the end date of the study.
- Presently, select cell G6 and write down 0 (as 0 means alive).
Next, proceed to the third patient. His admission date is 25 Feb 2021 and his last follow-up date is 10 Dec 2021. And at that time, he was alive. The last follow-up day means the last date of tracking. It was no longer possible to track after that day. So, the status at the end date is uncertain.
- Accordingly, move to cell G7 and type a ? (Question mark).
- Similarly, make inputs in cells in the G8:G14 range.
Read More: How to Calculate Time in Excel (16 Possible Ways)
Step 02: Compute the Follow-up Time of Each Patient
In this part of the article, we’ll figure out the follow-up time for each of the patients in this study. Let’s see the process in detail.
📌 Steps:
- At first, select cell H5 and enter the following formula.
=IFS(G5=1,((D5-C5)/365)*12,G5=0,((F5-C5)/365)*12,G5="?",((D5-C5)/365)*12)
Here, we’ve used the IFS function. It returns the value associated with the first condition that is satisfied.
In this formula, there are 3 conditions and 3 corresponding return values for them.
- In the first condition, if the value of cell G5 equals to 1, then the formula will return the value of ((D5-C5)/365)*12 in cell H5. We divided by 365 to get it in years and then multiplied it by 12 to get the final output in months.
- Output → ((44474-44201)/365)*12 → 8.97 (in cell H5, it’s 9 because of ignoring decimals)
- If we want to understand the output of the formula for cell H6, follow next. Here, cell G6 has the value of 0. So the return value will be ((F6-C6)/365)*12 in cell H6.
- Output → 17
- Following this, hit ENTER.
- At this time, bring the cursor to the corner of cell H5 and it will look like a plus (+) sign. Actually, it’s the Fill Handle tool. Then, double-click on it.
Immediately, it copies the formula to the remaining cells in the Follow-up Time column. Also, Excel shows the output on these cells in the blink of an eye.
Read More: How to Calculate Total Time in Excel (6 Suitable Examples)
Similar Readings
- Timesheet Formula in Excel (5 Examples)
- How to Calculate Hours and Minutes for Payroll Excel (7 Easy Ways)
- [Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)
- Excel Timesheet Formula with Lunch Break (3 Examples)
- Excel Formula To Calculate Time Worked
Step 03: Find out Median Follow-up Time
It’s the final step in this approach. All’s well that ends well. So, without further ado, let’s dive in!
📌 Steps:
- Afterward, go to cell H15 and paste the following formula.
=MEDIAN(H5:H14)
In this formula, we used the MEDIAN function to return the median of a group of numbers in the H5:H14 range.
- Henceforth, hit ENTER.
So, it’s the median follow-up time of that study. That’s all from me in this topic.
Read More: How to Create an Injection Molding Cycle Time Calculator in Excel
Median Follow-up vs Median Survival
Median follow-up and median survival time are both commonly used terms in the medical sector. Generally, we use this concept in the analysis of cancer patients. Let me differentiate these two terms with simple examples.
Suppose you are an oncologist and started a study with cancer patients in January 2020. Also, you set an end date for the study of June 2022. So, this is a 30-month-long case study. Many patients cannot survive during the study. Many others will endure until the end. Some patients will stop following up before the end date. So, the follow-up time for each patient should be different. The median of all this follow-up time is the median follow-up time.
On the other hand, median survival time is a time point in the research period. It indicates the time when 50% of the total patients are still alive. In the survival analysis graph, where the S(t) function meets 0.50 on the survival curve, its corresponding time is the median survival time.
Practice Section
For doing practice by yourself, we have provided a Practice section like the one below in the worksheet on the right side. Please do it by yourself.
Conclusion
This article explains how to calculate median follow-up time in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, Exceldemy, a one-stop Excel solution provider, to explore more.
Related Articles
- How to Calculate Percentage of Time in Excel (4 Suitable Examples)
- How to Calculate Time Difference Between AM and PM in Excel
- Excel Calculate Hours between Two Times after Midnight (3 Methods)
- How to Subtract Minutes from Time in Excel (7 Methods)
- How to Calculate Cycle Time in Excel (7 Examples)
- Calculate Elapsed Time Between Two Dates in Excel (5 Methods)
Bonjour,
Really enjoyed reading your article. Here you enter the end status manually, but i understand that it could be done with formula, but can’t figure out how. show me some light on this.please.thank you very much.
Hello Elaine,
First, it feels good to see your interest in learning it. That motivates us to work hard.
Now, coming back to your question. Here you intend to enter the End Date Status automatically. Yes, that’s a very good thought. In this era of automation, why should we lag behind? Hahahaha…. Okay, let’s get to the solution now.
There could be 4 separate conditions. First, the Last Follow-up Date could be before the End Date of the study and the patient is dead at the Last Follow-up Date. Second, the Last Follow-up Date could be after the End Date of the study and the patient is dead at the Last Follow-up Date. Third, the Last Follow-up Date could be before the End Date of the study and the patient is alive at the Last Follow-up Date. And the fourth, the Last Follow-up Date could be after the End Date of the study and the patient is alive at the Last Follow-up Date. So, how can we determine the End Date Status for these situations? Let’s see the steps below.
• Firstly, go to cell G5 and enter the following formula.
=IFS(AND(E5=1,D5<F5),1,AND(E5=1,D5>F5),0,AND(E5=0,D5<F5),"?",AND(E5=0,D5>F5),0)Here, we used the IFS function where we inserted those 4 conditions as logical_test and their output as value_if_true. Further, we used the AND function to join two conditions together.
• Finally, press ENTER.
That’s all from me on this. Don’t forget to visit our website, Exceldemy, a one-stop Excel solution provider, to explore more.
Stay well and healthy. Happy Excelling ☕.