Excel Lab #$1$: Descriptive Stats, Tables and Graphs

$2022$ W Excel Lab $1-$ Descriptive Stats Tables and Graphs Instructions January $2022.$docx Page $1$ of $10$

This tutorial requires Excel skills you were exposed to in your other program courses and additional formulas for statistics, pivot tables and graphs.

IF$,$ COUNT, AVERAGE, MEDIAN,

MODE.SNGL$,$ QUARTILE.EXC,

QUARTILE.INC, STDEV.S$,$ MIN, MAX

For additional help with Excel refer to:

$$ any Excel texts you may have

$$ online help via Lynda.com, Office Help or YouTube.

$1.$ Starting Excel

Open Office $2019$ Excel

Note: make sure you are saving your file often to ensure you don$`$t lose any of your work. $2.$ Entering Data Manually & Saving the Worksheet

Data Description:

The dataset given on the next page represents a random sample of the weights, in pounds $($lbs$.),$ of $50$ college students from two different classes. Female students have been designated with a $0$ and male students have been designated with a $1.$ Columns A and B contains data from class $1$; columns C and D contains data from class $2.$

Enter the data $($next page$)$ into A$-$D in your open Worksheet as shown.

Do not type the row number... this column is already in the Excel worksheet provided.

When you have finished entering the data, check that it has been entered correctly.

If any errors are found, click in the cell and retype.

Data errors will make the rest of the work incorrect, so you need to ensure there are no data entry errors.

Save the worksheet to a usb stick with the name $$Excel Lab $1$ followed by your initials. Example: Excel Lab$1$ SR

$**$ Remember to routinely save your worksheet so all your work is saved $**$

Excel Lab #$1$: Descriptive Stats, Tables and Graphs

$2022$ W Excel Lab $1-$ Descriptive Stats Tables and Graphs Instructions January $2022.$docx Page $2$ of $10$

Student Weights Data.

The dataset below represents a random sample of the weights, in pounds $($lbs$.),$ of $50$ college students from two different classes. Female students have been designated with a $0$ and male students have been designated with a $1.$ Columns A and B contains data from class $1$; columns C and D contains data from class $2.$

Excel Lab #$1$: Descriptive Stats, Tables and Graphs

$2022$ W Excel Lab $1-$ Descriptive Stats Tables and Graphs Instructions January $2022.$docx Page $3$ of $10$

$3.$ Combine & Label Data Columns

Since the class weights have been entered into two columns, A and C$,$ these two columns must now be combined $($or stacked$)$ into just one column to facilitate statistical analysis. The corresponding columns indicating female or male must be combined and kept with the associated weights as well. To do this:

$$ Select and copy A$1$:B$25$ to cell G$2$

$$ Label column G to $$Weights $($in lbs$.)$ and column H to $$Gender Code $(0=$female, $1=$male$)$

$$ Label column F to $$Class$$

$$ Enter $$Class $1$ in the column F cells beside the class $1$ data you just copied.

$$ Select and copy C$1$:D$25$ to cell G$27$

$$ Enter $$Class $2$ in the column F cells beside the class $2$ data you just copied $4.$ Code Data

Convert the current categorical data $$Gender Code$$ to text so that the categorical data is easier to read. To do this:

Label column I as $$Gender Word$$

In cell I$2$ enter the logical formula $=$IF$($H$2=0,$Female$,$Male$)$ Copy the formula to cells I$3$:I$51$

Your Worksheet should now look like below $($please note not all data is shown$)$:

Class

Weights $($in lbs$.)$

Gender Code $(0=$female,

$1=$male$)$

Gender Word

Class $1$

$181$

$1$

Male

Class $1$

$103$

$0$

Female

Class $1$

$93$

$0$

Female

Class $1$

$181$

$1$

Male

Class $1$

$187$

$1$

Male

Class $1$

$102$

$0$

Female

Excel Lab #$1$: Descriptive Stats, Tables and Graphs

$2022$ W Excel Lab $1-$ Descriptive Stats Tables and Graphs Instructions January $2022.$docx Page $4$ of $10$

Excel Lab #$1$: Descriptive Stats, Tables and Graphs

$2022$ W Excel Lab $1-$ Descriptive Stats Tables and Graphs Instructions January $2022.$docx Page $5$ of $10$

$6.$ Create a table of Descriptive Statistics and Histograms of the Sample Weights for each named range

Make a copy of the data table $($the one with class, Weight in lbs$.,$ Gender Code and Gender Word columns$)$ into a new worksheet of your Excel file.

Sort the data by the Gender Word column and the Weights in lbs$.$ column; Sort is under the Data tab

Define Named Ranges:

Select only the weights for the females.

In the Formulas tab select Define Name and name the data FemaleWeights

Select only the weights for the males.

In the Formulas tab select Define Name and name the data MaleWeights

Select all the weights.

In the Formulas tab select Define Name and name the data AllWeights.

Create a table of calculated Sample Statistics for each of the Named Ranges: formulas for each cell are shown below; your file should show the numbers not the formulas.

Note: the formulas for IQR are based on the cells that contain the Q$1$ and Q$3$ values.

Excel Lab #$1$: Descriptive Stats, Tables and Graphs

$2022$ W Excel Lab $1-$ Descriptive Stats Tables and Graphs Instructions January $2022.$docx Page $6$ of $10$

Create Descriptive Statistics using Data Ana