InjuryRateAnalyzer - Washington St Dept of Health

The InjuryRateAnalyzer is a Windows based Excel application. It compares county or EMS region injury rates to Washington state injury rates. Through color coding, it can help identify injury rates either higher or lower than the state rate. This can be done by age group or for all ages combined. You can find information on how to create InjuryRateAnalyzer workbooks here.

Download

Nonfatal Injury Hospitalizations - Years 2003-2007 Aggregated^†

Fatal Injuries - Years 2003-2007 Aggregated

Quick Start

Directions

Set Excel security settings

Excel’s macro security settings must be set to allow your computer to run the InjuryRateAnalyzer. To check the macro security settings, first open Excel. From the menu bar at the top of the Excel window, go to "Tools->Options...". This causes the Options dialog box to open. Choose the "Security" tab. In the dialog box, towards the bottom on the right-hand side press the button "Macro Security..." In the Security dialog box that opens, under the "Security Level" tab, you should see four options: Very High, High, Medium, and Low. Choose the medium option. Click "OK" in the "Security" dialog box. Click “OK” in the "Options" dialog box. The InjuryRateAnalyzer will now be able to run.

Download and unzip an InjuryRateAnalyzer workbook

The InjuryRateAnalyzer workbooks can contain a lot of data. So they are compressed into a zip file. After downloading an InjuryRateAnalyzer ZIP file, unzip the file. Typically you can do this by simply double-clicking on the zipped file. Most Windows based computers offer native support for ZIP files. If not, you need to use a program like WinZip or PKZip. If you still cannot unzip the file, email the injury data manager at injury.data@doh.wa.gov. The injury data manager will email you an unzipped, uncompressed InjuryRateAnalyzer file.

Open InjuryRateAnalyzer

Running InjuryRateAnalyzer

The type of analysis is controlled through a "Settings" dialog box. In Excel 2000, 2002 and 2003, It is accessed from the menu at "RateAnalyzer->Settings". In Excel 2007, it is accessed through the ribbon's "Add-ins" tab. Clicking the "Calculate" button in the main InjuryRateAnalyzer control box (dialog box) will perform the chosen analysis.

Explanation of Controls

InjuryRateAnalyzer menu

From the InjuryRateAnalyzer menu item, you can control different aspects of the application. The most important are the settings, accessed by going to "RateAnalyzer->Settings...". Selecting this menu item will cause the "Settings" dialog box to open.

Settings Dialog Box

Analysis Type frame: Choosing the "counterpart of Washington State worksheet" allows the InjuryRateAnalyzer to compare each cell's rate to the corresponding rate in the "Washington State" worksheet. Using this option, you can analyze how a county’s or EMS region’s rates differ from the statewide rates.

Choosing the "counterpart in 'All Ages' column on same worksheet" allows InjuryRateAnalyzer to compare each cells rate with the corresponding "All Ages" rate in the same row of the same worksheet. Using this option, you can analyze differences in rates in age groups compared to the rates for all ages combined.

Confidence Level frame: This frame allows you to set the confidence level in your analysis to 95%, 99% or 99.9%. Setting one of these options results in InjuryRateAnalyzer only comparing rates that are significantly different to the chosen confidence level from the reference rates. The reference rates are either in the Washington State worksheet or in the "All Ages" column. See "Analysis Type frame" above for the difference between the two. The "None" option causes the InjuryRateAnalyzer to compare all rates without using confidence levels.

Analysis Target frame: These options specify which sheets should be analyzed. The options are either the visible worksheet or all worksheets. Depending on the speed of your computer, specifying the "all worksheets" option could result in the analysis taking a long time. Choosing the "only the visible worksheet" is much quicker.

Hide Rows and Unhide Rows

InjuryRateAnalyzer only contains rates when the corresponding count is 5 or greater. Therefore, many cells do not have data. Instead, they contain asterisks. As a result, often entire rows of data contain only asterisks. "RateAnalyzer->Hide Rows" hides these cells. "RateAnalyzer->Unhide Rows" uncovers these hidden rows.

Zoom All Pages

This menu item opens a dialog box that allows you to zoom all the pages in the workbook to the same magnification. This is useful to do a quick color analysis of all the counties or EMS regions. For example, after performing an analysis on all the worksheets in the workbook, you could zoom the pages down to a magnification level of 25%. Then you click though all the worksheets to get a broad overview of unusual rates.

InjuryRateAnalyzer Control Box

This is where you perform an analysis of a workbook. The control box consists of a "Calculate" frame, and one or more criteria frames to the right of the "Calculate" frame.

Calculate frame

Pressing the "Calculate" button causes InjuryRateAnalyzer to analyze the rates using the displayed criteria. It relies on settings you can choose through the menu item "RateAnalyzer->Settings."

The "Add Criteria" button will add a criteria frame. You can have up to four criteria frames.

Criteria frame(s)

Each criteria frame’s heading is the name of a color. Each frame contains two numbers that you set. These two numbers define a rate multiplier interval. To search for rates that are less than the reference rates (either Washington State or the "All Ages column"), choose an interval from 0 to 1. The reference rate is set as Washington State, but can be changed to the “All ages column” using the menu item “RateAnalyzer->Settings.” Unless you change the color, these will show in yellow. To find rates that are larger than the reference rates, set the interval from 1 to some large number, such as 1000. Unless you change the color, these will show in green.

The confidence level has been set as 95%. You can change the confidence interval using the menu item "RateAnalyzer->Settings."

Other Features

Every cell that has a rate contains additional information as an Excel comment. A cell has a comment if it has a small red triangle in the upper left-hand corner. Placing the mouse curser over any red triangle opens a comment box. These comment boxes contain the following information:

For most locations, (4) and (5) will very similar. But if a location has a large portion of the state's population, such as King county, the Washington state rate and the rest of Washington state rate could be very different.

Statistical Calculations

The injury rates in InjuryRateAnalyzer are assumed to follow a Poisson distribution. To calculate confidence intervals, InjuryRateAnalyzer follows the DOH Guidelines for Using Confidence Intervals for Public Health Assessment. See Section 4.4 Crude and age-specific rates. Specifically, for counts greater or equal to five and less than 100, the confidence intervals the application calculates directly from the Poisson distribution. For counts 100 and over, the application uses the normal approximation.

To determine whether each cell’s rate is significantly different from the Washington State rates or All Ages rate, InjuryRateAnalyzer uses the Chi-squared test for a two-way table.

Limitations

Nonfatal Injury Hospitalization InjuryRateAnalyzer Applications

Hospitalization counts in Nonfatal Injury Hospitalization InjuryRateAnalyzer applications are obtained using a combination of Washington State's Comprehensive Hospital Abstract Reporting System (CHARS), Washington State VA hospitalization data, and Oregon hospitalization data, and military hospital data. Data is only available from Naval Hospital Oak Harbor starting in 2003. Since many residents of Island County are hospitalized in this facility, the worksheet for Island County contains data for the years 2003 through 2005. All other county and EMS region worksheets contain data for the years 2002 through 2005.

Hospitalization counts less than 11 are not displayed to maintain confidentiality.

Cross-border hospitalizations

Washington State does not have access to Idaho injury hospitalization data. Many Washington State residents from Asotin county are hospitalized in Idaho. As a result, the Asotin county worksheet is not included in Nonfatal Hospitalization InjuryRateAnalyzer applications.

Questions/Comments?

If you have questions or comments about InjuryRateAnalyzer, please contact the injury data manager at injury.data@doh.wa.gov.

About InjuryRateAnalyzer