Part 1 - Frequency Distribution of Categorical Data
Files Needed: SoftDrink.xlsx
The file above contains a list of 50 soft drink purchases with the label "Brand Purchased." Follow the instructions below for developing a frequency distribution of these soft drinks.
1. Open the SoftDrink.xlsx file in MS Excel.
2. Select any cell within the range of data. Using the ribbon (menus) at the top, insert a recommend pivot table into the spreadsheet. (Hint: You should see one that is a frequency distribution of the brands purchased. Your PivotTable should appear in another worksheet at the bottom).
3. Create an appropriate PivotChart to display your frequency distribution of soft drinks (Inside the same worksheet at the PivotTable is preferred).
4. Apply an appropriate chart style to your new chart. (There isn't a specific one required - just one that formats the chart for a professional report you would be providing upper management)
5. Change the title of your chart to say "Distribution of Soft Drinks Purchased."
6. Remove the legend from the chart.
7. While your chart is selected in the spreadsheet, you will notice there is a "+" symbol for "Chart Elements." Enable the check box for Axis Titles.
8. Click on the Horizontal Axis Title and replace it with Soft Drink.
9. Click on the Vertical Axis Title and replace it with Frequency.
Part 2 - Frequency Distribution of Quantitative Data
Files Needed: Audit.xlsx
The file above contains a list of 50 observations with the label "Audit." Follow the instructions below for developing a frequency distribution using quantitative data.
1. Open the Audit.xlsx file in MS Excel.
2. Select any cell within the range of data. Create a PivotTable based on the data in a new worksheet.
3. Drag the Audit Time field to the Rows area and then to the Values area.
4. Change the value field to display the Count instead of Sum.
5. Right click on any of the audit time cells. Choose Group from the list of options that appear.
6. When the Grouping dialog box appears, enter your Starting (min number), Ending (max number), and in the By enter the results of
the formula in the next step.
7. Using the formula for class width from the lecture slides, calculate the width of your classes if you wanted to have 4 class categories. (Hint: Largest Data Value - Smallest Data Value divided by the number of classes)
8. Change "Row Labels" in your PivotTable to read "Audit Time (days)," Count label to read "Frequency," and "Grand Total" to read "Total."
9. Using the recommended charts, insert a Histogram for the quantitative data.
10. Change the title to read "Histogram for Audit Time Data."
11. Remove the legend from the chart & insert axis titles and change them to read "Frequency" and "Audit Time (Days)" as appropriate.
12. Choose an appropriate style for your chart.
13. Right click on any bar within the chart and choose "Format Data Series."
14. In the Format Data Series options set the Gap Width to 0.
Your manager tells you that they would like to be able to see the frequencies in a pie chart showing the percentage each audit time (days) appears in the data. You need to calculate the relative and percent frequency for this data.
15. In a new worksheet, copy the frequency distribution as values (Hint: right click, paste option).
16. Add two new columns for Relative Frequency and Percent Frequency.
17. Using the lecture slides, enter formulas in both columns that will calculate the relative and percent frequency (there should be a sum in the total row).
18. Insert an appropriate pie chart that illustrates your percentage calculations.
Attachment:- New WinRAR archive.rar