From WikiEducator
Jump to: navigation, search

Description of activity and Excel instructions:Histogram

Dataset: actors.xls

Create the bins

First we need to define the bins (also called intervals or classes) that Calc will use to create the histogram. The minimum data point is 31 and the max is 76. We'll use a bin width of 5, and make bins from 30 to 80:

  • In one of the columns near the data, enter the upper value of the bin range for each bin: "30", "35", "40", "45", etc. up to "80".

The frequency count in each bin can be automatically counted using the FREQUENCY function. The FREQUENCY function is an array function, returning values to a range of cells.

  • Select the cell to the right of the value "30".
  • Select Insert > Function....
  • Scroll down the list of functions to select FREQUENCY.
  • Click on Next>>.

The Function Wizard will display.

The FREQUENCY function requires two arguments: data and classes.

  • Click on the Shrink button, OpenOfficeShrink.png, next to the data field.
  • Highlight the actor data, not including the Age label.
  • Click the Maximize button, OpenOfficeMaximize.png, to return to the full dialog box.
  • Use the same procedure to fill in the range for the classes (bins).

The completed formula will display in the Formula window.

  • Click OK.

The frequencies in each bin display next to the bin upper limit. So you can remember what's what:

  • Enter "Bin" in the cell above the bin values.
  • Enter "Frequency" in the cell above the frequencies.

Create the histogram

  • Choose Insert > Chart....

The Chart Wizard dialog box opens.

  • On 1. Chart Type:
    • Select Column.
    • Select the Normal version.
    • Click Next>>.
  • On 2. Data Range:
    • Click on the Shrink button, OpenOfficeShrink.png, next to the Data range window.
    • Highlight the full range of bins and frequencies.
    • Click on the Maximize button, OpenOfficeMaximize.png.
    • Click Data series in columns.
    • Check the box First column as label so the class levels are used as labels for the x-axis.
    • If you include the header labels, check the box "First row as label."
    • Click Next>>.

No revisions are necessary on 3. Data Series.

  • Click Next>>.
  • On 4. Chart Elements:
    • Enter a title
    • Enter labels for the x and y axes
    • Uncheck Display grids for both x axis and y axis.
    • Uncheck Display legend.
  • Click Finish.

The chart will be inserted into the open sheet.

  • Move the chart to be situated next to the frequency data.
  • To remove the space between the bars, as required for a histogram, double click on the graph to enter edit mode (a gray border will surround the chart).
  • Right click on a bar.
  • Choose Object Properties....
  • Select the Options tab.
  • Under Settings, set the Spacing to 0%.

Click OK.

You now have a histogram, not a bar chart.

Return to the OLI Histogram page to complete the interpretive exercises.