COVID Cases Analysis

COVID-19 Analysis
Since early 2020, everything we do has been impacted by the new COVID-19 virus pandemic. Cases were unknown in the US before January, but have spread to every jurisdiction. Original fears were that the pandemic would overwhelm health care facilities. Efforts were made to "flatten the curve" of infection rates to slow the progress of the disease. The Virginia Department of Health provides a number of statistics to help understand the spread of the disease. They first tracked cases and deaths by county, then later began providing more details by age, by sex, and by race. They provide detailed data at the daily level. The data for this project have been extracted and summarized to the week level. In this project, you will use Excel to analyze a summary of some of these data and learn to make informed comparisons.

You will begin with an Excel template which has been loaded with cumulative summary data for the 3 northern Virginia counties of Fairfax, Loudoun, and Prince William (the county where Manassas is located). For simplicity, the data have been summarized to weekly totals, beginning with Week 13 You will create formulas that display more meaningful information to help understand the data, graph key measures to show trends, and answer several questions in the cells provided in the spreadsheet.

First, some definitions.
Week number: This is the sequence number of weeks this year. Data provided begins with Week 13 which ran from March 22 to 28, and runs through Week 43 which ended Oct 24th.
COVID Cases: This is the cumulative number of cases reported at the end of the week indicated. Cases are widely reported in the news, often without much context.
COVID Hospitalizations: This is the cumulative number of patients hospitalized as of the end of each week. To save space, these data have been deleted from the dataset.
COVID Deaths: This is cumulative number of deaths as of the end of each week.
Cumulative Rates: Cumulative means the total number reported as of the end of each week, counting since the beginning of the pandemic. These never go down, only up. These are NOT the number of new cases reported that week, but the total reported since the beginning.
Weekly Rates: These are the number of new cases, hospitalizations or deaths reported for the first time in that week. You will calculate these rates from the cumulative data and see that these data do go up and down.
Normalized rates: These are rates per 100,000 population. Since reporting locations vary widely in population, you expect more cases in a big county than a small one. Normalization divides the raw rate by the population in units of 100,000 which simplifies and allows for fair comparisons among jurisdictions.

In this Project, raw cumulative count data are provided. You will create formulas to build weekly counts, then to convert these to normalized data. You will graph some of the key statistics and draw some conclusions.

PART 1 - DEVELOP WEEKLY STATISTICS COLUMNS

PART 2 - CREATE 2 GRAPHS OF THE NORMALIZED WEEKLY DATA
Your first graph will be "Weekly CASES per 100,000 Population", and your second will be "Weekly DEATHS per 100,000 Population".

PART 3 - SUMMARY STATISTICS FOR EACH COUNTY

PART 4 - QUESTIONS AND OBSERVATIONS
In the boxes provided at the bottom of the spreadsheet, answer the following questions:
Q1: Of the 3 counties, which has had the lowest Normalized Weekly Case rates of infections over most of the period? You can get an "eyeball" answer from the graph, or better, by comparing the Average Weekly rates you calculated in the Stats blocks above.
Q2: Considering the population statistics information about the 3 counties given in Rows 8 - 15, which factors might tend to explain your answer to Question 1?
Q3: What events or changes in policy might explain the differences in infection rates seen in Weeks 13-26 (March-June) compared to Weeks 27-43 (July-October)?
Q4: Looking at the Normalized Weekly Deaths columns after the weeks 13-26, are you surprised at the low values for Deaths per 100,000 in each of the Counties? How do these data compare with the impression you get from listening to news commentators about the pandemic impact?

FINALLY: REFLECTIONS
Give your reflections on the project. What did you learn about Excel that helped you most in this project? For example, you might reflect on what you learned about organizing many similar calculations, or what were the most useful Excel functions, or what problems you encountered. What did you discover about COVID infections in local counties? Just share your insights.