Overview

Overview#

Google Sheets is excellent for its low scare-factor and its free availability. Google Sheets will get you from point A to point B and with only an internet browser. Microsoft Excel can do a bit more. In the context of this class, the only advantage of Excel is the availability of the Analysis ToolPak plugin for regression analysis. Python or R can do a lot more. You might get a job from your Python abilities alone, but that won’t happen with Excel/Sheets.

Use Google Sheets if you are someone who needs to manipulate data or make statistical calculations, but not so often that you should suffer the investment required to use languages like Python or R or to learn/pay for other applications.

Basics#

Formulas: Not 1+1 but =1+1#

Arithmetic can be done as you would expect, as long as you enter = and then the formula. Without the equals sign, you will only have text.

Cell References: A1 and Sheet1!A1#

You can reference the value of another cell using its column and row coordinate. For example, A1 refers to the value in column A, row 1.

_images/sheetsFormula.svg

If you would like to reference the value in another sheet named Sheet1 (another tab in the same workbook), you can do that with the syntax =Sheet1!A1.

Cell Ranges: A1:A2, A:A and A1:B2#

You can select a range of cells with the mouse or by specifying a range of cell coordinates. When using coordinates, there are three types of ranges you might use:

  1. Single column subset, A1:A2. This will select the cells in column A from row 1 to row 2.

  2. An entire column, A:A. This will select every cell in column A.

  3. Rectangular selection, A1:B2, This will select A1:A2 and B1:B2. Note, you can use A1:C1 to select all cells in row 1 for columns A-C.

  4. An entire row, 1:1. This will select the entirety of row 1.

Selecting a range of cells will be useful later when we use formulas. Note, if you use the reference =A1:B2 by itself, as shown below, you will get back an error because the 2x2 range cannot be inserted into a single cell.

_images/sheetsRange.svg

Copying and Pasting Cell References ($A$1 vs A1)#

To leverage the power of Sheets, you should be copying and pasting your references and formulas as much as possible. There are three types of references that behave differently when copying and pasting. A plain =A1 reference:

  1. =A1, relative referencing

  2. =A$1, mixed referencing where the column is relative and the row is absolute

  3. =$A1, mixed referencing where the row is relative and the column is absolute

  4. =$A$1, absolute referencing

_images/sheetsAbsoluteRow.svg

Fig. 60 Absolute Row referencing.#

Rows are numbered and columns are lettered. A dollar sign in front of the number will make the row referencing absolute. Similarly, a dollar sign in front of the letter will make the column referencing absolute.

_images/sheetsAbsoluteColumn.svg

Fig. 61 Absolute Column referencing.#

Formulas#

Google Sheets offers hundreds of functions across several categories. We are especially interested in the statistical, math, and logical categories.

Mathematical and Statistical#

Below is a selection of commonly used functions in Google Sheets. Their purpose should be apparent from the name:

Function

Sample Usage

Notes

AVERAGE

AVERAGE(A1:A10)

CORREL

CORREL(A1:A10, B1:B10)

Correlation, same as PEARSON.

COVAR

COVAR(A1:A10, B1:B10)

MEDIAN

MEDIAN(A1:A10)

PERCENTILE

PERCENTILE(A1:A10, 0.5)

VAR

VAR(A1:A10)

This is SD\({^{+}}^2\).

VARP

VARP(A1:A10)

SUM

SUM(A1:A10)

STDEV

STDEV(A1:A10)

This is SD\(^{+}\), not SD, per [FPP07].

STDEVP

STDEVP(A1:A10)

This is SD, per [FPP07].

Probability Distributions#

Function

Sample Usage

Notes

NORMDIST

NORMDIST(x, mean, std dev, cumulative)

BINOMDIST

BINOMDIST(num successes, trials, probability success, cumulative)

CHIDIST

CHIDIST(x, degrees of freedom)

Right-tailed

CHISQ.DIST

CHISQ.DIST(x, degrees of freedom, cumulative)

Left-tailed

TDIST

TDIST(x, degrees of freedom, tails)

NORMINV

NORMINV(probability, mean, std dev)

TINV

TINV(probability, degrees of freedom)

Two-tailed inverse

Logical#

Function

Syntax

Example

IF

IF(logical_expression, value_if_true, value_if_false)

IF(A1 > 0, A1, 0)

AND

AND(logical_expression, [logical_expressions, ...])

AND(A1 > 0, A1 < 2)

OR

OR(logical_expression, [logical_expressions, ...])

OR(A1 > 2, A1 < -2)

AVERAGEIF

AVERAGEIF(criteria_range, criterion, [average_range])

AVERAGEIF(A:A, ">0", B:B)

SUMIF

SUMIF(criteria_range, criterion, [sum_range])

SUMIF(A:A, "taxi", B:B)

Lookup Functions#

Function

Syntax

Example

XLOOKUP

XLOOKUP(search_key, lookup_range, range_result, missing_value, match_mode, search_mode)

XLOOKUP("Shaq", A:A, B:B)
Contents