Statistics Homework 1 & 2
Getting Started with Excel
Variables
Excel Window
Variable names are used as the column heading names in the dataset
This dataset has 5 variables
Excel Window
Cell Reference
Formula Bar
Column letter Reference
Row Number Reference
Spreadsheet name
Add Spreadsheet
Scroll Zoom
Content of selected cell
Active cell
Excel Window Cell Reference
Column letter Reference
Row Number Reference
Content of selected cell
Active cell
The cell reference and the active cell are referencing the same location on the spreadsheet. The cell reference is constructed using the column letter and row number of the active cell.
Cell Reference Content of selected cell
Active cell Selected cell
Excel Window
The active cell and the selected cell are not always referencing the same location in the spreadsheet. Use an equal sign (=) when entering mathematical formulas into cells. Cell references can be used when constructing formulas. The cell references using in the formula in the diagram above are relative cell references. Relative references are color coded.
Relative cell
references
Cell Reference Content of selected cell
Excel Window
Filler handle is used to copy formula contents from the selected cell to other cells in the same column or the same row. Select the filler handle and drag it over the cells where the formula is to be copied. Relative Row number and column letter references will change as the filler handle is dragged over the cells.
Absolute cell
reference
Selected cell
Cell Reference Content of selected cell
Add dollar signs ($) in front of column letter and/or row number references to create absolute cell references. For absolute cell references, when the filler handle is used to copy formula contents from the selected cell to other cells in the same column or the same row, row number and column letter references will NOT change if they are preceded by a dollar sign as the filler handle is dragged over the cells.
Excel Window
Built in Function
Selected cell
Cell Reference Content of selected cell
Type built in Excel function names, directly into cells, to perform calculations using cell references identifying other cells in the spreadsheet as the function arguments.
Excel Window
Formatting Charts
Double-click a chart in Excel to activate the Format Chart Area
Resize a chart by dragging the chart handles. All chart content should be legible, and of a size that is easily readable without having to resize the document the chart is presented in.
Chart handle
Select the chart title to
delete it
Formatting Charts
Use the Add Chart Element menu to add vertical and horizontal axes to charts in Excel
Formatting Charts
Figure 1: Annual Food Spending in dollars by customer
For all Excel charts submitted for grading in this class: • Meaningful Vertical and Horizontal axes names are
required • Axes title font should be 11pt or larger • No chart titles in the charts • Do not use ‘Bin’ as a horizontal axis title • Horizontal values should be flat and fit on the
chart • Charts should be numbered • Chart numbering should be placed at the bottom
of the chart • All charts should include a brief description at the
bottom • Histograms should have 5-8 bars • Center align, in the center of the page, all charts in
your document • All chart information, including numbering and
description, should be presented on a single page • All charts are to be referred to as ‘Figure’ • Charts are to be copied and pasted into a
document for submission (do not submit Excel spreadsheet containing charts)
For all Excel tables (and created elsewhere) submitted for grading in this class: • Table font should be 11pt or larger • All tables should be numbered • Table numbering should be placed at the top of
the table • All tables should include a brief description at the
top • Center align, in the center of the page, all tables in
your document • All table information, including rows, numbering,
and description, should be presented on a single page . That is, DO NOT slice the table rows across multiple pages in your document.
• All tables are to be referred to as ‘Table’ • Tables are to be copied and pasted into a
document for submission (do not submit Excel spreadsheet containing tables)
Formatting Charts
Data Analysis Toolpak Data Analysis Toolpak access in Excel is required in this class:
• Select File • Select Options • Select Add-ins • Select Analysis Toolpak • Click the Go button • Click ok
Data Analysis Toolpak
Once installed, the Data Analysis Toolpak provides access to the Data Analysis tools on the Data menu
Generate a Random Sample
Use the Sampling Tool, from the Data Analysis window, to generate a random sample of values from a variable column in your dataset
Column references where your variable is located
Sample size
Where to place the sample in your Excel workbook