39 terms

Excel Chapter 2

relative cell reference
indicates a cell's relative location from the cell containing the formula. the cell references in the copied formula change relative to the position of the copied formula.
absolute cell reference
provides a permanent reference to a specific cell. the cell reference in the copied formula does not change. appears with a dollar sign before the column letter and row number ex: $A$4
mixed cell reference
combines an absolute cell reference with a relative cell reference. either the column letter or he row number that the absolute reference remains fixed while the other part of the cell reference that is relative changes in the copied formula. ex:$A4 or A$4
circular reference
if a formula contains a direct or an indirect reference to the cell containing the formula. usually cause inaccurate results
a predefined computation that simplifies creating a formula that performs a complex calculation. more than 400 of these. start with a equal sign, the name, then arguments in parentheses
the rules that dictate the structure and components required to perform the necessary calculations.
specify the inputs that are required to complete the operation
formula autocomplete
displays a list of functions and defined names that match letters as you type a formula. double click the function name from the list
function screentip
a small pop-up description that displays the function's arguments after you type function name and open parentheses
sum function
totals values in two or more cells and displays the result in the cell containing the function. the ellipsis in the function syntax indicates you can add as many additional ranges as desired
calculates the arithmetic mean, for the values in a range of cells.
finds the midpoint value, which is the value that one half of the data set is above or below
analyzes an argument list to determine the lowest value
analyzes an argument list to determine the highest value
tallies the number of cells in a range that contains values you can use in calculations, such as numerical and date data, but excludes blank cells or text entries from the tally.
tallies the number of cells in a range that are blank
tallies the number of cells in a range that are not blank, cells that contain data, whether a value, text, or formula
quick analysis
a set of analytical tools you can use to apply formatting, create charts or tables, and insert basic functions
nested functions
occurs when one function is embedded as an argument within another function.
today function
displays the current date. updates the function results when you open or print the workbook. must include parentheses.
now function
uses the computer's clock to display the date and military time. will change everytime the workbook is opened
logical functions
test the logic of a situation and return a particular result
lookup and reference functions
are useful when you need to look up a value in a list to identify the applicable value
financial functions
are useful to anyone who plans to take out a loan or invest money
if function
most common. returns one value when a condition is met or is true and returns another value when the condition is not met or is false.
logical test
a formula that contains either a value or an expression that evaluates to true or false. value if true or false statements can contain text, cell references, formulas, or constants
lookup table
a range containing a table of values or text that can be retrieved. should contain at least 2 rows and two columns. must arrange data from lowest to highest and include only the lowest value in the range
the lowest value for a category or in a series
vlookup function
accepts a value, looks the value in a vertical lookup table, and returns a result. used for a search for exact matches or for the nearest value that is less than or equal to the search value. 4 arguments: lookup_value, table_array, col_index_number, [range_lookup]
lookup value
the cell reference of the cell that contains the value to look up
table array
the range that contains the lookup table. must be absolute and cannot include column labels for the lookup table
column index number
the column number in the lookup table that contains the return values
hlookup function
horizontal lookup table. argument: row_index_number
pmt function
calculates payments for a loan with a fixed amount at a fixed periodic rate for a fixed time period. requires 3 arguments and 2 optional: rate, nper, pv, fv, type
the periodic interest rate per payment period. divide the annual percentage rate by the number of payment periods in one year
the total number of payment periods. multiply the number of years by the number of payments in one year.
the present value of the loan. the result is a negative value because it represents your debt so type a minus sign in front of the present value cell reference
range name
a word or string of characters assigned to one or more cells. absolute references. can contain 255 characters, but it must begin with a letter or a underscore. can use combination of upper- or lowercase letters, numbers, periods, and underscores throughout this. cannot include spaces or special characters. cannot be identical to the cell contents
name manager dialog box
used to edit, delete, and create range names.