Name: Group #: Class Time:
Practicum – Module 7: Data Visualization in Excel
Descriptive Statistic Formulas
For the data on amount of waste (trash, recycling) thrown away per month (weight,
in pounds) by generation (Gen X vs. Millennial) create a descriptive table, starting at
cell F1. Type the labels and the corresponding Excel command to calculate the
values. Fill in the empty spaces in this table:
NOTE: Range is the range of data in the Weight column (B).
Descriptive
Excel command Value
Measure
Mean =average(RANG
E)
Std. Deviation =stdev(RANGE)
Minimum =min(RANGE)
Median =median(RANG
E)
Maximum =max(RANGE)
Mode =mode(RANGE)
N (# observations)
? What can you say about the distribution of weight given the statistics above? Are
mean, mode, and median similar or different? What does that tell you?
Data Visualization
Using the Waste Weight data, show the following:
Histogram Dot Plot
1. Sort the weight data in ascending order (click on cell label B1, then select
“sort &filter > filter”, then sort using the filter buttons).
2. Create a new column, next to the sorted weights, label it "Dots".
3. Enter 1 in the first cell under "Dots".
4. In the next cell down in the "Dots" column, enter the formula: =IF(B2=B3,
C2+1, 1)
5. Drag this formula down to fill the column for each row of data.
6. Create a scatter plot with the weight data on one axis and the "Dots" data on
the other axis using the "Insert > Charts" option.
Histogram – Frequency Distribution
To create a histogram:
- we need to define the bin weight ranges,
- then count the number of observations in each range.
Let’s use 9 bins:
1. Calculate each bin width using the max and min in the table of descriptives
you calculated: (MAX-MIN)/(number of bins - 1):
2. Create bin ranges starting from the specific value 102.4 in a new column.
Below it, increment each subsequent cell by the bin width calculated in step 1
(e.g., =D2+15).
It should look like 102.4; 117.4; 132.4; 147.4; 162.4; 177.4; … ; 222.4 .
These numbers are the boundaries of bin ranges.
3. Activate the Analysis ToolPak: Go to "File > Options > Add-Ins > Analysis
ToolPak" or "Tools > Excel Add-ins".
4. Use the Histogram tool from the Data Analysis options under the "Data" tab.
5. In the Histogram dialog, set:
a. “Input Range” to your weight data column.
b. “Bin Range” to the column with your bin ranges.
c. Choose to output to a New Worksheet.
6. Finally, create a bar chart from the histogram data by selecting "Insert >
Chart > Bar". You might want to try recommended charts first to see if it
suggests the proper chart for you. Avoid using Excel's built-in histogram chart
type since you've manually calculated the frequency distribution.
Relative Frequency
1. Calculate the relative frequency of each bin Divide the count by the total (i.e.,
C2 cell should read: =B2/sum(B$2:B$10))
2. Create a new sheet and copy the Bin column (A) into the new sheet.
3. Copy and paste value the calculated relative frequency column in step one into
the new sheet next to column Bin.
4. Graph the relative frequencies
a. This chart should look very similar to the Histogram you just created.
The y-axis should be different though.
? What is the difference between the Frequency distribution and Relative Frequency
distribution graphs? What information does each provides that the other doesn’t?
? Can you make a comment about what proportion of people are disposing of too
much trash (compared to recycling)? If yes, what proportion and if not, what other
information do you need? (Think carefully about the criteria for labels)
Excel Histogram
1. Select the Weight column in the first sheet and then create a histogram chart:
InsertàChartàHistogram
2. Double click on one of the bins created. In the panel on the right you have the
option to decide the number of bins in the histogram by number of bins or
width of bins. (if clicking on the bin didn’t bring the number of bins, try clicking
on one of the labels on horizontal axis.)
? What do you think is a good number(s) for number of bins? Why?
Upload
Upload TWO files: this file, completed and saved as a PDF and the Excel file.