how to make a survivorship curve in excel

Mastering Excel: Create Survival Curves in No Time

By Jessica White
AD

Creating a survivorship curve in Excel can be an effective way to visually represent data, especially when dealing with tracking survival times. This step-by-step guide will walk you through the process, making it accessible for both beginners and experienced users.

Before diving in, ensure your data is set up correctly: you need a column for the event status (1 for occurred, 0 for not occurred) and another for the time to event. Let's assume your data looks like this:

Time to Event Event Status
5 1
10 0
3 1

Preparing the Data

For accurate calculations, you need to sort and format your data. Click on any cell within your data range, then go to 'Data' in the menu, choose 'Sort & Filter', and select 'Sort' to arrange your data in ascending order based on 'Time to Event'.

AD
How to Make a Bell Curve in Excel (Easy Step-by-Step Guide)
How to Make a Bell Curve in Excel (Easy Step-by-Step Guide)
a graph shows the time and temperature for different types of plants
a graph shows the time and temperature for different types of plants
an animal and other animals are shown in the diagram, which is labeled as type i
an animal and other animals are shown in the diagram, which is labeled as type i

Next, we'll create a 'Survivor' column. In the cell below 'Event Status', use the formula "=IF(A2=1,1,COUNTIF($A$1:A2,">"&A2&"))" (replace 'A2' with your 'Time to Event' start cell). This will calculate the number of survivors for each time point. Copy this formula down to apply it to the rest of your dataset.

Creating the Curve

Now that your data is prepared, let's create the curve. We'll use a scatter plot with smoothed lines to visualize the survivorship.

How to make a male_female ratio chart in Excel
How to make a male_female ratio chart in Excel
Master Excel in 30 Days: Your Roadmap to Data Mastery! 🚀
Master Excel in 30 Days: Your Roadmap to Data Mastery! 🚀

Creating the Scatter Plot

With your data still selected, click on 'Insert' in the menu, then 'Scatter' and choose 'Scatter with Smooth Lines'. Excel will automatically create a scatter plot using your 'Time to Event' and 'Survivor' columns.

Customizing the Plot

To make the plot look like a standard survivorship curve, customize it by right-clicking on the plot area, selecting 'Format Selection', then 'Add Axis' to add a secondary y-axis. Set the minimum and maximum y-values for your new axis to 0 and 100, respectively. This will ensure your survivorship curve starts and ends at the correct points.

AD

Finally, add a title to your chart, format the axis labels, and adjust the line color to make your survivorship curve clear and professional.

Survivorship curves are powerful tools for visualizing survival data, but remember that the survival function they represent is subject to the assumptions of the statistical model used to derive it. Always contextualize your curves appropriately.

AD