HistogramsSlide 2Slide 3Slide 4Slide 5Slide 6Slide 7Slide 8Slide 9Slide 10Slide 11Slide 12Slide 13Slide 14Slide 15Slide 16Slide 17Slide 18Slide 19Slide 20Math 115a Mathematics for Business Decisions, part IHistogramsMath 115aMath 115a HistogramsA histogram is a table/graph that sorts data into pre-specified categories, or bins. HistogramsMath 115a HistogramsYou can find Histograms in Excel 2003 under:ToolsData AnalysisHistogramsIf you don’t see “Data Analysis” under Tools:Go to Add Ins… under ToolsSelect both Analysis Toolpak and Analysis Toolpak-VBA (you may need your original installation disc) Histograms, Excel 2003Math 115a HistogramsYou can find Histograms in Excel 2007 under:Data tabAnalysis group, Data AnalysisHistogramsIf you don’t see the Analysis group in the Data tab:Go to Add Ins… in Excel Options under the Office buttonSelect both Analysis Toolpak and Analysis Toolpak-VBA (you may need your original installation disc) Histograms, Excel 2007Math 115a Histograms HistogramYou’ll need to specify:Input RangeBin RangeLabels (if applicable)Output RangeMath 115a HistogramsInput Range:This is the range of cells that contains the data you want to sort.Enter this as a typical cell range in Excel:A2:A101, e.g. Histogram windowMath 115a HistogramsBin Range:The range of cells that contain your pre-specified bin ranges (i.e. your categories).Enter this as a typical cell range in Excel:H2:H10, e.g. Histogram WindowMath 115a HistogramsAgain, “bins” are your categories that you want to sort your data into.You must specify the intervals that you want, or else you’ll get some odd endpoints!Let’s say you want to sort the test scores into the categories 40-49, 50-59, 60-69, … , 90-99.You will designate these intervals by the right endpoint for each interval: 49, 59, 69, …, 99 Bins in ExcelMath 115a HistogramsExcel will read it as the following: Bins in ExcelBin: Understood as:49all data points 495949 < data points 596959 < data points 697969 < data points 798979 < data points 899989 < data points 99Math 115a HistogramsLabelsYou MUST be consistent:Check the box: ONLY if you included the column headers for both the input and bins.Leave unchecked: ONLY if your ranges contained only the data, and no headers. Histogram WindowMath 115a HistogramsOutput RangeInput only the cell in which you want the output to START. You should have several clear columns to the right of this cell, and several empty cells below this.NOTE: By default, “New Worksheet Ply:” is selected. When you choose “Output Range” it will automatically highlight the “Input Range” space. BE CAREFUL! You click in the box by “Output Range” before selecting a cell. Histogram WindowMath 115a HistogramsThe output from Excel repeats your bin limits, and also gives the frequency count.“More” should be zero. If not, your bin limits did not go high enough to accommodate your data. Outputlabels binsBin Frequency40-49 4949 150-59 5959 860-69 6969 470-79 7979 580-89 8989 790-99 9999 2More 0Math 115a HistogramsBins do not need to start at zero!Find the range of your datarange = maximum – minimumYour first bin limit should be reasonably above your minimum data pointGood rule-of-thumb: your first bin limit should be equal to your minimum value + bin widthYour last bin limit should be greater than your maximum data point Choosing appropriate bin limitsMath 115a HistogramsRule of thumb: start your bin size by the calculation: range of data 10Make your endpoints nice numbers, but make sure every bin has the same size intervalSee how your histogram looks:If it’s too “blocky”, increase the number of bins by decreasing the bin sizeIf it’s too detailed, decrease the number of bins by increasing the bin size Choosing the bin sizeMath 115a HistogramsHistograms are typically shown as a column graph.Use Excel’s Chart WizardSelect Column Graph as the typeIn “Data Range” select only the non-More Frequency cells.Click on the “Series” tab and enter the range of cells for labels for “X-axis labels” Graphing the histogramMath 115a Histograms Graphing window: DataMath 115a Histograms Graphing window: SeriesMath 115a HistogramsNOTE: Labels will always be centered under each column. Therefore it is not a good idea to label a column by its right endpoint. Labeling by the full interval (i.e. “40-49”) is acceptable only if you have enough room, and not too many bins. Usually it is best to have a column of midpoints for each interval, and label with these. (Remember, the label is centered under each column!) LabelsMath 115a HistogramsWe know grades can range from 0-100. Therefore, it makes sense to demonstrate this continuum of grades by no space between columns: SpacingMath 115a HistogramsRight-click on any columnSelect “Format Data Series”Select the “Options” tabSet “Gap width” to zero
View Full Document
Unlocking...