39 terms

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

function

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

syntax

the rules that dictate the structure and components required to perform the necessary calculations.

arguments

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

average

calculates the arithmetic mean, for the values in a range of cells.

median

finds the midpoint value, which is the value that one half of the data set is above or below

min

analyzes an argument list to determine the lowest value

max

analyzes an argument list to determine the highest value

count

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.

countblank

tallies the number of cells in a range that are blank

counta

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

breakpoint

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

rate

the periodic interest rate per payment period. divide the annual percentage rate by the number of payment periods in one year

nper

the total number of payment periods. multiply the number of years by the number of payments in one year.

pv

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.