Excel - Ch 2 Flashcards
Indicates a cell’s relative location from original cell; when you copy formula, cells referenced in formula change, but maintain the same relative difference (A12)
Relative Cell Reference
Provides constant reference to specific cell; cell reference does not change when copied; $ before Row & Column; used when there is a constant/fixed amount in formula ($A$12)
Absolute Cell Reference
Combines both types of references; $ in front of Column OR Row to denote which one is fixed; (A$12) or ($A12)
Mixed Cell Reference
Predefined computation that simplifies creating a formula that performs a complex calculation; Excel has 400+ types in 14 different categories
Function
FUCTION : Provides method for manipulating date & time status
Date & Time
=DATE
FUNCTION : Performs financial calculations (payments, rates, present/future value)
Financial
=RATE ; =PV ; =FV
FUNCTION : Performs logical tests & returns value of the test (if, and, or, not)
Logical
=IF ; =AND ; =OR
FUNCTION : Looks up values, creates links to cells or provides references to cells in a worksheet
Lookup & Reference
=VLOOKUP ; =HLOOKUP
FUNCTION : Performs common statistical calculations (sums, averages, median)
Statistical
=SUMS ; =AVERAGE ; =MEDIAN
Rules that indicate the structure & components required to perform the necessary calculations; function started by = followed by type, followed by arguments in enclosed in brackets
Syntax
Specify the inputs (such as cells, values, or arithmetic expressions) that are required to complete the formula; in some cases, multiple are required followed by commas
Argument
Displays list of functions & defined names that match letters as you type
Formula AutoComplete
Small pop-up description that displays function’s arguments; shows up after you type function name and opening bracket
Function ScreenTip
Totals values 1 one or more cells; more efficient than adding each cell
SUM Function
Finds midpoint value in a range; if uneven number of cells, the average is taken from the 2 mid points
Median Function
Calculates arithmetic average of values between range of cells
Average Function
Analyzes an argument list to determine lowest value
MIN Function
Analyzes an argument list to determine highest value
MAX Function
Tallies number of cells in a range that contain values you can use
COUNT Function
Tallies number of cells in a range that are blank
COUNTBLANK Function
Tallies all cells in a range except if it is blank (all values & text)
COUNTA Function
Occurs when there is a function inside another function
Nested Function
Set of analytical tools you can use to apply formatting, create charts/tables, and insert basic functions (shows at bottom right of a selected range)
Quick Analysis
Displays current date in a cell; updates when document is opened or saved
TODAY Function
Uses computer clock to display current date/time; updates when a documents is opened or saved
NOW Function
Tests specified criteria to see if it is true or false, then returns one value when condition is met (true) or another value when condition is not met (false)
IF Function
=IF(logical_test, [value_if_true], [value_if_false])
Comparison Operator : < >
Less than / Greater than
Comparison Operator : <= =>
Less than or Equal to / Greater than or Equal to
Contains either a value or an expression that evaluates to TRUE or FALSE; requires comparison between at least 2 variables (different cells)
Logical Test
Accepts a value and looks for value in left column of a table array, returns for another located in same row from a specified column
VLOOKUP Function
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Cell reference of the cell that contains the value to look up
Lookup Value
Range that contains the lookup (input) table
Table Array
Column number in the lookup table than contains the return values
Column Index Number
Determines how the VLOOKUP Function handles lookup values that are not an exact match for the data in the lookup table; OPTIONAL
Range Lookup
Range containing table of values & text from which data can be retrieved; should contain at least 2 rows and 2 columns (not including headings)
Lookup Table
Lowest value in a category or series
Breakpoint
Horizontal Lookup Table
HLOOKUP
=HLOOKUP(lookup_value, _table_array, row_index_num, [range_lookup])
Calculates payments for a loan for a fixed amount at a fixed periodic rate for a fixed time
PMT Function
=PMT(rate, nper, ps, [fv], [type])
Interest rate per period (if annual % is 12%, each month would be 1%)
Rate
Total number of payment periods (monthly, weekly, annually, etc)
Nper
Present Value of the loan
Pv
