# 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

## Leave a Reply