In this activity we will:

$$ Construct a Line Graph using Excel.

$$ Learn how to analyze a dataset.

$$ Practice classifying variables by their type: quantitative or categorical.

$$ Learn how to handle variables whose values are numerically coded.

$$ Learn how to tally our data into a table of counts and percentages.

$$ Construct a Pie Chart using Excel.

$1.$ Constructing Line Graph using Excel

Renee is keeping track of the total number of employees working at her company over time in the Excel Spreadsheet:

Month: $012345$

# of Employees: $0118910$

Construct a Line Graph to show the change over time.

$$ Open the M$2$ Excel Worksheet and open a new Tab, rename it $$Line Graph$$

$$ Copy the table above, including the titles, into the Excel worksheet

$$ Highlight the entire table, and click Insert

$$ Click on Recommended Charts, and select the Line Graph

$$ You created the Line Graph!

$[$Insert your Line Graph here$]$

$2.$ Analyzing a dataset using Excel

Background to dataset:

Clinical depression is one of the most common mental disorders in the United States. For some individuals, major depression can result in severe impairments that interfere with or limit one$$s ability to carry out major life activities. In $2017,$ an estimated $11$ million U$.$S$.$ adults aged $18$ or older had at least one major depressive episode with severe impairment. This number represented $4\mathrm{.}5\%$ of all U$.$S$.$ adults. $($Source: nimh.nih.gov, $2021)$

In a study conducted by the National Institutes of Health, $109$ clinically depressed patients were separated into three groups, and each group was either treated with one of two active drugs $($Zoloft or Prozac$),$ or no drug at all $($Placebo$).$ For each patient, the dataset contains the treatment used, the outcome of the treatment, and several other interesting characteristics.

Here is a summary of the variables in our dataset:

a$.$ Hospital: The patient's hospital, represented by a code for each of the five hospitals $(1,2,3,4,$ or $5)$

b$.$ Treatment: The treatment received by the patient $($Zoloft$,$ Prozac, or Placebo$)$

c$.$ Outcome: Whether or not a recurrence of depression occurred during the patient's treatment $($Yes or No$)$

d$.$ Time: The length of time of the patient's participation in the study $($number of days$)$

e$.$ D$-$Time: The length of time that the patient was depressed before treatment $($number of days$)$

f$.$ Age: The age of the patient in years, when the patient entered the study

g$.$ Sex: The patient's sex assigned at birth $(1=$ Female, $2=$ Male$)$

For each of the variables in our dataset, identify the following characteristics from the Excel spreadsheet:

i$.$ Name $($of the variable in the spreadsheet$)$

ii$.$ Description $($what does the variable represent?$)$

iii. Data Type $($quantitative or qualitative?$)$

iv$.$ Range of values, if quantitative

Answers:

a$.$

b$.$

c$.$

d$.$

e$.$

f$.$

g$.$ The variable $$Sex$$ represents the patient$$s sex assigned at birth, it is a qualitative data type and does not have a range of values.

$[$Continue answering the rest of the variables above, g$.$ is done for you as an example$]$

Complete the steps $1-5$ below:

$1.$ Open the M$2$ Excel Worksheet and examine the Data tab.

$2.$ Recode the variable $$Sex$$ with the labels "Male" and "Female" $($see below$)$

Often it is easier to use labels for categorical variables that are as close as possible to the meanings of the categories. Now we will do that in Excel:

$$ Click on the column header above the Sex column. In this case, Sex is in column G$,$ so click on the column header labeled G$.$ This will select the entire column of Sex values

$$ From the Edit Menu $,$ choose Find & Select pull$-$down to then select $"$ Replace... $"$

$$ On the $$Find$$ tab, In the first textbox labeled $$Find what:$$ enter $"1".$

$$ Select the $$Replace$$ tab, and in second textbox labeled $$Replace with:$$ enter "Female".

$$ Now click the button labeled Replace All $.$ This will replace all of the $"1"$ values in our selected column with the word "Female". Hit OK $.$

$$ Now do the same thing for males:

$$ In the first textbox on the Find tab labeled $$Find what:$$ enter $"2".$

$$ In the second textbox on the Replace tab labeled $$Replace with:$$ enter "Male"

$$ Click the Replace All button, and then click OK and then the Close button.

Notice that the column Sex now contains the meaningful labels "Female" and "Male" where before it contained $"1"$ and $"2"$ codes

$3.$ Create a Pivot Table to show counts:

$$ Click on the cell with the variable label Hospt.

$$ In the Insert menu, select $$Pivot Table$.$

$$ Click OK$.$

Excel will create a new worksheet with a blank table. We have to tell Excel how to construct the table:

You should see a window titled PivotTable Fields with a single item in the Field list that is our "Hospt" variable.

$$ Check the box next to Hospt

$$ Drag the Hospt item to the window in the PivotTable Fields window where it says $$Rows$($it might already be there$).$

$$ You should be seeing the counts for each field in the $$Total$$ column. If not, go back to the PivotTabl