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.</code></pre>
<!-- /wp:code -->

=IF(A1=1,TRUE)
- Returns the logical value TRUE.</code></pre>
<!-- /wp:code -->

=STDEV(number1,[number2],…)
- Estimates standard deviation based on a sample (ignores logical values and text).</code></pre>
<!-- /wp:code -->

=LEN(A1)
- Returns the number of characters in a text string.</code></pre>
<!-- /wp:code -->

=SUM(A1, A2)
- Adds all the numbers in a range of cells.</code></pre>
<!-- /wp:code -->

=TRIM(A1, A2)
- Removes all spaces from a text string except single spaces between words.</code></pre>
<!-- /wp:code -->

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</code></pre>
<!-- /wp:code -->

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