Histograms Capital Credit Union

# Histograms Capital Credit Union

Télécharger la présentation

## Histograms Capital Credit Union

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
##### Presentation Transcript

1. HistogramsCapital Credit Union Issue: Analyze credit card balances for Capital Credit Union customers using a frequency distribution and histogram. Objective: Use Excel 2007 to develop a frequency distribution and histogram for credit card balances. Use ten class intervals. Data file is Capital.xls

2. Histograms-Capital Credit Union • Open the Excel file “Capital.xls” • The file contains 300 observations – The last row is 302 – Column 2 (B) contains the balances

3. Histograms-Capital Credit Union c Use Excel’s “Max” function to determine the largest valueMax(b2:b301) Note: ignore the cell number in the RED circle

4. Histograms-Capital Credit Union c Use Excel’s Min function to determine the smallest value Min(b2:b301) Note: ignore the cell number in the RED circle

5. Histograms-Capital Credit Union • Compute Class Width • Compute range as Maximum – Minimum: 1493 – 99 = 1394 • Excel: 2^k >= n (300) • 2^8=256 and 2^9=512 • Take 10 instead of 9 because of convenience • Divide range by 10 to construct 10 classes: 1394 / 10 = 139.40 • Round up to \$150.00 (width) • Intervals are: • \$90 to < \$240 (90+150) • \$240 to < \$390 • \$390 to < \$540 • Etc…

6. Histograms-Capital Credit Union Intervals are: \$90 to < \$239.99 \$240 to < \$389.99 \$390 to < \$539.99 \$540 to < \$689.99 \$690 to < \$839.99 Go on ….. ….. …. ….. …< \$1589.99

7. Histograms-Capital Credit Union • Construct the Bins (The upper limits of each class • Intervals are: • \$90 to < \$239.99

8. Histograms-Capital Credit Union • Select the Data tab • Select Data Analysis • Select Histogram • OK

9. Histograms-Capital Credit Union Input Range is Column B. Bin Range is Column F – The range of Bins that you entered. Select New Worksheet Ply Select Chart Output OK

10. Histograms-Capital Credit Union Frequency legend This is Excel’s default output. It should be modified for most applications. The first change would be to delete the Frequency legend , change the Bins title to Balances and increase the size of the chart using the resize handles. Bins Title Sizing Handle

11. Excel’s default on a histogram is to include a More category. Remove the More category from the chart. Select the chart, right mouse click, Select Data, Edit Frequency. In the Edit Series dialog box, change Series Values to \$B\$11 rather than \$B\$12. Click OK.

12. Histograms-Capital Credit Union • Right click on any of the bars in the Histogram • Select Format Data Series

13. Histograms-Capital Credit Union Set Series Overlap and Gap Width to Zero to format the Histogram as shown

14. Histograms-Capital Credit Union To put border colors around the bars of the histogram , select Border Color, Choose Solid line, Select the Color arrow, and Choose Dark Blue, Text 2 Theme Color.

15. Histograms-Capital Credit Union Change the Bins categories to 0-239.99, 240-389.99, etc. and delete the More bins and frequency. Change the Histogram title to Credit Card Balances. See next slide for results of these changes.

16. Histograms-Capital Credit Union