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