3.7 Using Excel for Descriptive Statistics

The image shows an Excel spreadsheet with a histogram dialogue box open. The spreadsheet contains two columns labelled “Hights” and “Bin Range”. Here is a detailed breakdown:

The histogram dialogue box titled “Histogram” contains the following fields and options:

• Input:
  • Input Range:
  • Bin Range:
• Labels

• Output options:
  • Output Range:
  • New Worksheet Ply
  • New Workbook
  • Pareto (sorted histogram)
  • Cumulative Percentage
  • Chart Output

Buttons at the bottom labelled “OK,” “Cancel,” and “Help.”

The image shows a Microsoft Excel spreadsheet with data in two columns labelled “Year” and “CPI” (Consumer Price Index). The data, starting from row 2, lists the years from 2003 to 2012 and their corresponding CPI values. The cells containing this data are highlighted.

To the right of the spreadsheet, an Insert Chart menu is displayed, showing various chart options. The menu specifically focuses on line charts and area charts with 2-D and 3-D variations. Below the chart icons, there is an option labeled “More Line Charts…”.

Here is the data in table format:

The image displays a Microsoft Excel interface for creating a chart. On the left side of the image, there is a spreadsheet with a table containing two columns labelled “Year” and “CPI” (Consumer Price Index). The table includes the following data:

To the right of the table is the “Insert Chart” pane, where various chart types can be selected. The “Line” chart type is chosen, which is highlighted in gray. Next to the selection pane, there are preview images of line charts, with the rightmost chart being a simple line chart titled “CPI,” representing the data from 2003 to 2012 with a line showing an upward trend.

Below the chart previews are the “OK” and “Cancel” buttons for confirming or cancelling the chart insertion.

This image shows a spreadsheet application with a table and a chart. The table is in columns A and B with the following data:

To the right of the table, there is a line chart representing the CPI (Consumer Price Index) data over the years. The x-axis shows the years ranging from 2003 to 2012, and the y-axis shows the CPI values. The plotted line on the chart indicates a gradual increase in CPI over the given years.

There is a context menu open on the chart, showing the following options:

• Fill
• Outline
• Delete
• Reset to Match Style
• Font…
• Change Chart Type…
• Select Data…
• 3-D Rotation…
• Add Minor Gridlines
• Format Major Gridlines…
• Format Axis…

The image displays a screenshot of a spreadsheet and a line chart, along with a formatting pane on the right side.

The left part of the image contains a table with two columns and eleven rows. The table includes:

In the center part of the image, there is a line chart that displays these data points, and the chart is titled “CPI.” The x-axis represents the years from 2003 to 2012, and the y-axis represents the CPI values ranging from approximately 180 to 240.

The right part of the image shows the “Format Axis” pane with options to adjust the axis bounds, units, and other formatting settings. The current settings show the minimum bound set to 180 and the maximum bound to 240, with major units set to 10 and minor units to 2.

The image shows an open Microsoft Excel window titled “OER Excel Instructions_Excel – Saved.” The Excel workbook has multiple tabs (File, Home, Insert, Draw, Page Layout, Formulas, Data, Review, View, Help, Chart Design, Format) visible in the ribbon. Below the ribbon, there are various chart-related options such as Add Chart Element, Quick Layout, Change Colors, and Chart Styles.

A spreadsheet is shown with two columns of data:

To the right of the data columns, there is a line chart depicting the CPI values over the years from 2003 to 2012. The chart has the title “CPI” above it and the x-axis labelled “Axis Title” with yearly increments from 2003 to 2012.

On the right side of the chart, there is a “Chart Elements” panel with checkboxes for Axes, Axis Titles, Chart Title, Data Labels, Data Table, Error Bars, Gridlines, Legend, Trendline, and Up/Down Bars.

Below the spreadsheet, there is a tab selection area with the active tab labeled “Line Chart”. Other visible tabs include Pie chart and Bar chart, Freq Table, Pivot Table, Histogram, Mean, Median, and Mode.

The image shows an Excel spreadsheet with the “Home,” “Insert,” “Page Layout,” “Formulas,” “Data,” and “Review” tabs visible at the top. The focus is on a portion of the spreadsheet displaying columns A, B, C, and D from rows 1 to 5.

Column A is labelled “Lengths” in cell A1. Below this, cells A2 to A5 contain the following numbers respectively: 16, 17, 19, and 20.

In column C, there are three rows labelled as follows:

• Cell C2: Mean
• Cell C3: Median
• Cell C4: Mode

In cell D2, there is a formula input box at the top, displaying the formula =AVERAGE(A:A). This indicates that the mean of the values in column A is being calculated.

The image shows a spreadsheet application with a table and some calculations displayed. There are various tabs like “File,” “Home,” “Insert,” “Page Layout,” “Formulas,” “Data,” and “Review” visible at the top, indicating this is likely a Microsoft Excel interface.

In the content area:

On the right side, there is a column with statistical calculations:

• Mean: 27.33
• Median: =MEDIAN(A:A) (shows the formula for calculating the median of column A)
• Mode: (empty, no value provided)

The image shows a Microsoft Excel spreadsheet window. The top menu bar contains options like “File,” “Home,” “Insert,” “Page Layout,” “Formulas,” “Data,” and “Review.” Below the menu bar is a formula bar with the function “=MODE(A:A)” entered in it. The spreadsheet contains the following data:

Column A contains numeric values 16, 17, 19, and 20. Column C includes statistical functions such as Mean, Median, and Mode, while Column D displays corresponding values: 27.33, 27, and a mode formula “=MODE(A:A)”.

This image is a screenshot of a Microsoft Excel spreadsheet. The Excel window is open, displaying the toolbar with various tabs and options. Visible are tabs labelled Home, Insert, Page Layout, Formulas, Data, Review, View, and Help. The Data tab is currently selected.

Under the toolbar, there is an area with options related to data tasks like refreshing data, sorting, filtering, data types, and analysis tools.

Beneath this, there is the spreadsheet area. The spreadsheet consists of rows labelled numerically on the left (1, 2, 3, etc.) and columns labelled alphabetically at the top (A, B, C, etc.).

In this Excel sheet, the column “A” from row 1 to row 12 contains the following data:

The image shows a partial view of a spreadsheet with a statistical analysis tool dialogue box open in the foreground.

The spreadsheet in the background has columns labelled A and B. The columns contain numerical data ranging from 20 to 30.

The dialogue box is titled “Descriptive Statistics” and contains various input fields and options:

Input

• Input Range: Text box containing the value “$A$1:$A$28”.
• Grouped By: Radio buttons for “Columns” (selected) and “Rows”.
• Labels in the first row: Checkbox (unchecked).

Output options:

• Output Range: Radio button (selected) with a text box containing the value “C$1”.
• New Worksheet Ply: Radio button.
• New Workbook: Radio button.
• Summary statistics: Checkbox (unchecked).
• Confidence Level for Mean: Checkbox (unchecked) with a text box containing the value “95” and a percentage symbol.
• Kth Largest: Checkbox (unchecked) with a text box.
• Kth Smallest: Checkbox (unchecked) with a text box.

There are buttons for “OK”, “Cancel”, and “Help”.

By selecting the desired options and columns, this tool can be used to generate descriptive statistics for the data in the spreadsheet.

The image displays a partial view of an Excel spreadsheet. Below is the description of its content translated into accessible HTML:

• Column A contains the header “Ages (Years)” and various age values listed from cells A2 to A8.
• Between cells C2 and D2, the word “variance” is written in C2, and the formula “=VAR.S(A:A)” is entered in D2.
• Between cells C3 and D3, the words “standard deviation” are written in C3, and D3 is empty.

The worksheet is designed to calculate the variance of the ages listed in column A using the formula written in cell D2.

The image is a screenshot of an Excel spreadsheet with a table that includes one column of data. Column A is titled “Ages (Years)” and contains the values: 9, 9.5, 9.5, 10, 10, 10, 10.5, and 10.5.

To the right of the table, in column C and D, two statistical calculations are shown:

• Variance is calculated in cell D2 with the result 0.5125.
• Standard deviation is calculated in cell D3 using the formula =STDEV.S(A:A).

The image is a screenshot of an Excel spreadsheet displaying data and tools from the “Data Analysis” dialogue box. The screenshot includes the following key elements:

• Excel Interface:
  • The standard Excel tabs are visible at the top (File, Home, Insert, etc.).
  • The “Data” tab is selected.
  • The “Data Analysis” button is visible in the “Analysis” group on the ribbon.
• Spreadsheet Data:
  • A table titled “Ages (Years)” is present in column A, containing age values from 9 to 12.
  • There are additional calculations related to the data in columns C and D: “variance” and “standard deviation.”

• Data Analysis Dialog Box:
  • A pop-up window titled “Data Analysis” with a list of analysis tools.
  • The “Descriptive Statistics” option is highlighted.
  • Buttons for “OK”, “Cancel”, and “Help” are visible within the dialog box.

This configuration indicates that the user is leveraging Excel’s statistical tools to analyze the provided age data.

The image shows a spreadsheet and a “Descriptive Statistics” dialogue box from Excel. The spreadsheet has data in two columns and at least 20 rows. Column A is labelled “Ages (Years)” and contains the following values:

• Row 2: 9
• Row 3: 9.5
• Row 4: 9.5
• Row 5: 10
• Row 6: 10
• Row 7: 10.5
• Row 8: 10.5
• Row 9: 10.5
• Row 10: 10.5
• Row 11: 11
• Row 12: 11
• Row 13: 11
• Row 14: 11
• Row 15: 11.5
• Row 16: 11.5
• Row 17: 11.5
• Row 18: 11.5

Columns B and D are empty. Column C contains two calculations labelled:

• Row 2: “variance” with a value of “0.5125”
• Row 3: “standard deviation” with a value of “0.7158911”

Columns E and F are empty.

The “Descriptive Statistics” dialogue box, which appears to be floating above the spreadsheet, includes fields and options:

• • Input
    • Input Range:
    • Grouped By:
      • Columns
      • Rows
    • Labels in the First Row
  • Output options
    • Output Range:
    • New Worksheet Ply:
    • New Workbook
    • Summary statistics
    • Confidence Level for Mean:
    • %
    • Kth largest:
    • Kth smallest:

The image depicts a table titled “Ages (Years)” with statistical data. Below is the HTML representation of the table. Note that the rows corresponding to Standard Deviation and Sample Variance are highlighted in the table.

The image is a screenshot of an Excel spreadsheet. It features a table listing exam scores and calculations for quartiles.

Cell A1 contains the header “First exam scores”. Cells A2 to A7 contain the scores 33, 42, 49, 49, 53, 55, and 55.

Column C lists the quartiles: “Q1” in C2, “Q2” in C3, “Q3” in C4, and “Q3 – Q1” in C5.

In column D, D2 contains the formula “=QUARTILE.EXC($A:$A,1)”. The cells D3, D4, and D5 are empty.

The image displays a portion of a spreadsheet containing exam scores and quartile calculations.

The quartile calculations are listed in column D, starting from row 2. The calculated value for the first quartile (Q1) is 61, and the cell containing the formula for the second quartile (Q2) shows “=QUARTILE.EXC($A:$A,2)”. The other quartile values have not yet been computed.

The image shows a screenshot of an Excel spreadsheet. The worksheet contains two main areas: a list of exam scores and a computation of quartiles.

The left side of the spreadsheet lists First exam scores in column A:

The right side of the spreadsheet is a computation table with two columns: Quartile in column C and Value in column D:

The image is a screenshot of an Excel spreadsheet.

• Columns:
  • Column A is labelled “First exam scores” and has the following data:
    • A2: 33
    • A3: 42
    • A4: 49
    • A5: 49
    • A6: 53
  • Column B is empty.
  • Column C is labelled “Quartile” and has the following data:
    • C2: Q1
    • C3: Q2
    • C4: Q3
    • C5: Q3 – Q1
  • Column D is labelled “Value” and has the following data:
    • D2: 61
    • D3: 73
    • D4: 90
    • D5 contains the formula =D4-D2.
• Formula Bar: The formula bar shows =D4-D2, indicating that D5 is the result of subtracting the number in D2 (61) from the number in D4 (90).

This data seems to be part of an analysis of exam scores and quartile values.