Plotting Data Missing Points in Excel: A Blood Pressure Monitoring Solution
Blood pressure monitoring is an essential part of maintaining good health. By regularly checking and recording blood pressure readings, individuals can keep track of their health and identify any potential issues early on. However, creating visualizations of blood pressure data can be challenging, especially when there are missing points in the data. In this article, we will discuss how to plot blood pressure data in Excel, including how to handle missing points.
Setting up the Spreadsheet
To begin, let's set up a spreadsheet with three columns: date, systolic pressure, and diastolic pressure. Each row will represent a new reading. Additionally, there is a "Line Markers" option that can be used to indicate specific points in the data.
| | |
-------------------------------------------------------------------
01/01/2023 | 120 | 80 |
01/02/2023 | 125 | 85 |
01/03/2023 | 130 | 90 |
01/04/2023 | | 95 | M
01/05/2023 | 135 | 100 |
01/06/2023 | 140 | 105 |
In this example, there is a missing systolic pressure reading on 01/04/2023. The "M" in the Line Markers column indicates that this is a missing value. We will discuss how to handle this missing point in the next section.
Plotting the Data
To plot the data, we will use Excel's line chart feature. To do this, select the data range, including the headers, and then go to the "Insert" tab and select "Line Chart".
| | |
-------------------------------------------------------------------
01/01/2023 | 120 | 80 |
01/02/2023 | 125 | 85 |
01/03/2023 | 130 | 90 |
01/04/2023 | | 95 | M
01/05/2023 | 135 | 100 |
01/06/2023 | 140 | 105 |
In this example, there is a gap in the line chart on 01/04/2023, where there is a missing systolic pressure reading. To handle missing points like this, we can use one of two methods:
-
Method 1: Replace missing values with the average of the surrounding values. This method can be useful for smoothing out the data and making it easier to visualize trends. However, it can also lead to inaccuracies if the missing values are not representative of the surrounding values.
-
Method 2: Connect the points on either side of the missing value with a straight line. This method can be useful for showing the general trend of the data, but it can also make it difficult to see specific points in the data.
In this example, we will use Method 2 to connect the points on either side of the missing value with a straight line.
Handling Missing Values
To handle missing values, we can use Excel's "Connect missing data points with line" feature. To do this, select the line chart and then go to the "Design" tab and select "Select Data". In the "Select Data Source" dialog box, select the series that you want to modify and then click on the "Edit" button. In the "Edit Series" dialog box, check the "Connect missing data points with line" option and then click "OK".
| | |
-------------------------------------------------------------------
01/01/2023 | 120 | 80 |
01/02/2023 | 125 | 85 |
01/03/2023 | 130 | 90 |
01/04/2023 | | 95 | M
01/05/2023 | 135 | 100 |
01/06/2023 | 140 | 105 |
In this example, the missing systolic pressure reading on 01/04/2023 is now connected with a straight line to the surrounding points. This allows us to see the general trend of the data, even when there are missing values.
Plotting blood pressure data in Excel can be challenging, especially when there are missing points in the data. However, by using Excel's line chart feature and handling missing values appropriately, it is possible to create accurate and informative visualizations of blood pressure data. By regularly monitoring and visualizing blood pressure data, individuals can maintain good health and identify potential issues early on.
References
-
"How to Create a Line Chart in Excel". Microsoft Support. https://support.microsoft.com/en-us/office/create-a-line-chart-in-excel-3fc66bf5-3abd-425a-aa2a-898a09d3583f
-
"How to Handle Missing Data in Excel". Smartsheet. https://www.smartsheet.com/handle-missing-data-excel
-
"Blood Pressure Monitoring: What You Need to Know". Mayo Clinic. https://www.mayoclinic.org/diseases-conditions/high-blood-pressure/in-depth/blood-pressure-monitoring/art-20048105