Study Notes #4

Spreadsheets 1: Getting Started

Range – A group of cells selected or addressed together. It is defined by the cells in the upper left and lower right corners of the range.

Relative Addressing – cell locations are relative to the answer cell that references them (This is the common.)

Absolute Addressing – fixed cell or range address that doesn’t change when copied. (We add a $ sign.)

Pro Tip: Use the F4 key to quickly toggle from relative to absolute addresses in Excel

Cell Formulas

Cell Formula – An expression, beginning with an equal sign (=), that defines the operations which calculate the value for the cell.

• constant
• may contain mathematical operations
• may contain functions
• may reference a single cell, a range of cells, or no cells

Function – a standard-defined routine that can be used in formulas (i.e. SUM, TRIM, AVERAGE…)

Parameter – value or expressions required by a function to determine a result, defined by the function

• constant
• cell reference
• range reference
• other expression
• another function

Sample Functions

=UPPER(A1)

- Converts a text string to upper case letters.

=IF(A1=1,TRUE)

- Returns the logical value TRUE.

=STDEV(number1,[number2],…)

- Estimates standard deviation based on a sample (ignores logical values and text).

=LEN(A1)

- Returns the number of characters in a text string.

=SUM(A1, A2)

- Adds all the numbers in a range of cells.

=TRIM(A1, A2)

- Removes all spaces from a text string except single spaces between words.

Text String – a string of letters, numbers, and punctuations that are not treated numerically.

=SUBSTITUTE({text}, {old_text}, {new_text})

- where {text} is the cell to change, {old_text} is the string sequence to be replaced, and {new_text} is the new string in place of the old one.

=SUBSTITUTE(A14, "fox", "cat")
*case-sensitive

While the SUBSTITUTE function sounds similar to find/replace, it is used for different purposes. Find/replace gets rid of the old data, while SUBSTITUTE will not change the original cell, instead showing the transformed data in a new cell.

• different than find/replace
• does not change the original cell
• transformed in new cell