1 / 16

Histograms Capital Credit Union

Histograms Capital 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.

thy
Download Presentation

Histograms Capital Credit Union

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related