How to Calculate Median Follow-up Time in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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, 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.


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? 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.


How to Calculate Median Follow-up Time in Excel: with Easy Steps

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.

how to calculate median follow-up time in excel

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 the 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.

Determine Status at End Date

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 the date after the last follow-up.

  • So, go to cell G5 and write down 1 (as it means dead).

Status at End date of Patient 1

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 was 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).

Inserting End Date Status to calculate median follow-up time in excel

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 of 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.

Inserting End Date Status to calculate median follow-up time in excel

Read More: How to Calculate Percentage of Time in Excel


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:

  • 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.

Formula Breakdown

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)*128.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 a value of 0. So the return value will be ((F6-C6)/365)*12 in cell H6.
    • Output → 17
  • Following this, hit ENTER.

Compute the Follow-up Time of Each Patient

  • At this time, bring the cursor to the corner of cell H5 and it will look like a plus (+) sign. It’s the Fill Handle tool. Then, double-click on it.

Using Fill Handle Tool to get Follow-up Time

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.

Compute the Follow-up Time of Each Patient


Step 03: Find out the 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.

Find out Median Follow-up Time

So, it’s the median follow-up time of that study. That’s all from me on this topic.

Read More: How to Calculate Lead Time 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.

Practice Section


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Conclusion

This article explains how to calculate median follow-up time in Excel in a simple and concise manner. 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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Welcome to my profile! I'm thrilled to have you here. As a dedicated Naval Architecture and Marine Engineering graduate from the prestigious Bangladesh University of Engineering & Technology, I am deeply immersed in the realm of research and analysis. My current focus revolves around Microsoft Excel, where I engage in extensive work and conduct insightful research. Through this platform, I share articles that shed light on the vast possibilities of Excel. I'm also an avid reader and passionate traveler, constantly seeking knowledge and implementing it effectively in my work. Join me on this exciting journey as we explore Excel and optimize our productivity together.

2 Comments
  1. Reply
    Elaine F. Snelson Oct 19, 2022 at 3:05 PM

    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.

  2. 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 ☕.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo