Mapping Injury Prevention With Microsoft Excel

Abstract

BACKGROUND: 

Level I trauma centers must conduct injury prevention activities within their communities, despite limited resources available to them. To direct resources where they are most needed, injury prevention personnel at our trauma center use a novel method employing standard computer software. Although this method can be applied toward any injury population and prevention program, the example of targeting elderly falls prevention with A Matter of Balance classes is provided.

OBJECTIVE: 

To share an innovative, yet efficient, technique to direct injury prevention efforts using Microsoft Excel map charts.

METHODS: 

Using fictitious trauma registry data, an elderly falls rate is computed for each zip code. The top falls areas are then compared visually with areas where A Matter of Balance classes were conducted using Microsoft Excel map charts.

RESULTS: 

Disparate areas of outreach, as well as areas where resources are inefficiently utilized, present in a manner that is easily recognized and communicated.

CONCLUSION: 

Microsoft Excel map charts provide an effective and efficient means of displaying areas of greatest need, assessing disparities between those needs and outreach efforts that were conducted, and communicating with leadership.

Within the United States, unintentional injuries are among the top three causes of death for all age groups (^{Centers for Disease Control and Prevention, 2019}). To help combat this problem, the ^{American College of Surgeons Committee on Trauma (2014)} requires support for trauma injury prevention programs as part of the trauma accreditation process. Despite these facts, maintaining support for injury prevention can be difficult.

Resources are often limited within trauma programs (^{McDonald et al., 2007; Rosenblatt et al., 2019}). Therefore, it is vital to direct injury prevention to populations of greatest need. The need to assess disparities within prevention delivery is also very necessary. Furthermore, it is essential to be able to effectively communicate with trauma leadership to gain their continued support. As demonstrated by ^{Bechtel et al. (2021)}, trauma injury prevention personnel must be both resourceful and innovative to ensure that these needs are addressed.

The authors of the current article utilize an innovative method to guide and evaluate injury prevention efforts at WellSpan–York Hospital. Previously published literature has focused upon using novel computer software for mapping injury prevention (^{Stewart et al., 2017}). Conversely, the current authors' technique can be replicated by any trauma center using standard software that they already possess and are familiar with using.

Using data extracted from the trauma registry and Microsoft Excel, this technique can be applied toward any injury population or preventive program. This method highlights, via mapping, zip codes where injury prevention is most needed versus zip codes where outreach is conducted. Disparate areas are visually accentuated and present locations for the trauma center to prioritize their future outreach efforts.

OBJECTIVE

The purpose of this article is to share an innovative, yet efficient, technique to direct injury prevention efforts using Microsoft Excel map charts.

KEY POINTS

• Trauma centers must conduct injury prevention activities for accreditation purposes, as per the American College of Surgeons Committee on Trauma.
• Resources for conducting injury prevention efforts and outreach are often limited.
• The use of Microsoft Excel map charts allows for a quick visualization of areas of greatest need and outreach disparity.
• These map charts also provide a means of efficient communication with leadership for support of outreach programs.

METHODS

To implement this technique, the first step is to query the mechanism of injury and related demographics of interest from the trauma registry. Then compile the top zip codes with greatest number of patient injury with population rate factored in. To factor in population, the number of patients is divided into U.S. Census Department data for each respective zip code. The second step is to compile zip codes where the outreach is occurring for the chosen mechanism of injury. The final step is to map the results with Microsoft Excel to identify any areas of disparity. See Figure 1 for an overview of the process.

Figure 1.: Method for mapping and assessing disparity.

To demonstrate the method, we use the example of reducing elderly (age ≥60 years) simple falls (i.e., from ground level) through delivery of A Matter of Balance (MOB) classes. This example is applicable to many trauma centers, as elderly simple falls tend to be their greatest source of injury (^{American College of Surgeons, 2016}). All data are fictitious, but presented consistently throughout, to facilitate comprehension of the method.

Step 1: Identify Target Population

The first step is to query the trauma registry for mechanism of injury and related demographics, in this case simple falls with age 60 years or more, while reporting necessary patient factors (mechanism of injury, age, sex, residence zip code, and incidence zip code). For this example, we will apply the query to the most recent 5-year period. The incidence zip code can give insight into where the problem is occurring. Conversely, the residence zip code will display where to focus your outreach efforts. Both offer utility when looking at your data. Assessing the residence and incidence zip codes, it is clear that simple falls patients fell, primarily within the same zip codes where they were residing (Table 1). This allows for focus to be placed upon the residence zip code rather than the incidence zip code to offer the program where the population falling the most resides. This makes it more convenient for them to attend classes, which is useful, as the authors have found that, locally, dropouts most frequently report transportation and inconvenience as reasons for not completing the MOB program.

Table 1. - Summary Table of Simple Falls Patient Factors

Factor	M	SD	n	%
Mechanism of injury Simple falls	–	–	3,375	100
Age	81	29	–	–
Sex Female Male	– –	– –	2,440 935	72.3 27.7
Residence zip code 17019 17313 17315 17322 17331 17354 17356 17360 17361 17368 17401 17402 17403 17404 17406 17408	– – – – – – – – – – – – – – – –	– – – – – – – – – – – – – – – –	156 254 50 454 269 72 68 185 451 272 568 147 118 121 79 111	4.6 7.5 1.5 13.5 8 2.1 2 5.5 13.4 8.1 16.8 4.4 3.5 3.6 2.3 3.3
Incidence zip code 17019 17313 17315 17322 17331 17354 17356 17360 17361 17368 17401 17402 17403 17404 17406 17408	– – – – – – – – – – – – – – – –	– – – – – – – – – – – – – – – –	162 245 40 485 262 69 57 178 420 389 491 141 122 130 82 102	4.8 7.3 1.2 14.4 7.8 2 1.7 5.3 12.4 11.5 14.5 4.2 3.6 3.9 2.4 3

For the current example, 5 years of data were extracted. To achieve the average annual rate, the number of patients within each zip code must be divided by 5. Then, to factor in the population rate, the number of patients is divided into Census population data for each zip code (Figure 2) using zip code tabulation area and age filters (^{U.S. Census Bureau, 2021}). This permits the ability to determine the highest fall rate areas. For practicability, the authors target the top 10 areas. The data in the example indicate that 85.2% (2,877/3,375) of the elderly simple falls patients resided within the top 10 of the 16 elderly simple falls residence zip codes (Table 2).

Figure 2.: Elderly falls rate formula.

Table 2. - Average Elderly Falls Rate

Residential Zip Code	# of Falls Patients ≥60 Years	Average Annual # of Fall Patients ≥60 Years per Year	Population ≥60 Years of Age From Census	Average Annual Elderly Falls Rate per 1,000 Adults ≥60 Years
17019	156	31.2	7,100	4.4
17313	254	50.8	5,800	8.8
17315	50	10	3,107	3.2
17322	454	90.8	8,101	11.2
17331	269	53.8	6,109	8.8
17354	72	14.4	4,210	3.4
17356	68	13.6	2,798	4.9
17360	185	37	1,658	22.3
17361	451	90.2	6,256	14.4
17368	272	54.4	5,927	9.2
17401	568	113.6	9,520	11.9
17402	147	29.4	8,024	3.7
17403	118	23.6	1,900	12.4
17404	121	24.2	2,251	10.8
17406	79	15.8	3,521	4.5
17408	111	22.2	2,125	10.4

Step 2: Compile Outreach Conducted

The second step is to compile where the outreach is occurring by zip code. In the example, this is where MOB classes have been delivered. It is imperative to collaborate with other institutions in the area who may be conducting similar outreach for the same population. All outreach that has been conducted for that mechanism of injury, within that area, should be taken into account, regardless of who has conducted it. Otherwise, injury prevention professionals may be wasting their resources conducting outreach where it has already been completed. Our hospital participates within local coalitions with our peers. This has provided us with a means of sharing information and resources related to our top mechanisms of injury.

Step 3: Assess Disparity

The final step is to map and compare the results to identify any areas of disparity. To create maps, begin by creating a frequency table within Excel that summarizes the data by zip code. Then highlight the table, click “insert,” “maps,” and then “filled map.” Then right click on the map to select “format data series....” Finally, select the following series options: “automatic” for map projection, “only regions with data” for map area, and “show all” for map labels.

For the current example, a map displaying elderly simple falls is created (Figure 3A) along with a map displaying areas where MOB classes have occurred (Figure 3B). When comparing these maps, you should see similar concentrations of MOB classes conducted in relation to elderly falls areas. For maximum utilization of resources, zip codes with more falls should have more classes and zip codes with fewer falls should have fewer classes.

Figure 3.: Top 10 elderly falls areas (A) versus number of A Matter of Balance classes (N = 18) conducted (B). Note. Microsoft product screenshots reprinted with permission from Microsoft Corporation.

RESULTS

Viewing the example maps, it is apparent that zip code 17360 has remarkably dissimilar concentrations between fall rate and MOB classes conducted. Clearly, it is the most disparate area within our data subset. It had the highest fall rate, yet zero MOB classes were conducted there. Zip code 17408 is an additional disparate area, as it is within the top 10 highest fall rate areas yet had zero MOB classes conducted there. Zip code 17331 presents as an inefficient use of resources, as it received the highest concentration of MOB classes, despite having a lower concentration of falls. The remaining zip codes present as being adequately served.

DISCUSSION

Trauma injury prevention professionals often lack institutional support, as demonstrated by inadequate training provided to them upon their hiring. Typically, they receive their training from others who are either outside of their institution or outside of their specific job role (^{Newcomb et al., 2020}). Therefore, it is important to share knowledge and techniques among injury prevention professionals. One way to do this is to publish innovations for peers within the profession. According to ^{Blanchard et al. (2015)}, “Innovations have the capacity to improve the quality of experiences for everyone—if they are shared” (p. 322).

The current study allows a quick means of comparing areas of greatest prevention needs with areas where outreach has been conducted, detecting disparate areas, and easily communicating these findings with leadership to gain their continued support. With limited support, mapping can highlight outreach areas that may otherwise be overlooked or wasteful of resources. This technique does not require much time or skill to apply or interpret.

Unlike previously published mapping techniques, the standard software that is used is readily available. Most injury prevention professionals will likely possess Microsoft Excel and be familiar with its use. ^{Stewart et al. (2017)} expounded upon the use of mapping injury data for prevention purposes. However, their approach incorporated the use of writing Python script, which is a skill that most injury prevention professionals are unlikely to possess. Furthermore, Microsoft Excel did not have maps built into it at the time of their publication. To our knowledge, using Microsoft Excel to map injury prevention efforts has not been published to date.

CONCLUSIONS

Using Microsoft Excel map charts allows an efficient means of directing injury prevention and educational outreach to where it is most needed. It allows for quick detection of disparities between these areas and service delivery. It also allows an efficient means of communicating injury prevention activities and goals with leadership. We have found this technique most useful at our institution. Through sharing this innovation with our peers, we hope that others may also find it valuable.