Tutorial 3: Calculating Data with Formulas and Functions

New Perspectives Excel 2013

Tutorial 3: Calculating Data with Formulas and Functions

Key Terms

absolute reference A cell reference thatremains fixed when the cell formula is copied to a new location. (EX 133)

approximate match lookup A lookup where the lookup value falls within a range of numbers in the compare values in the lookup table. (EX 176)

argumentThe numbers, text, or cell references used by the function to return a value. (EX 133)

AutoFill A feature thatcopies content (text, values, or formulas) and formats from a cell or range into an adjacent cell or range. (EX 162)

compare values The categories used for matching to a specified lookup value in a lookup table. (EX 160)

comparison operator A symbol that indicates the relationship between two values. (EX 172)

constantA value in a formula that doesn’t change. (EX 135)

COUNT function The Excel function thattallies how many cells in specified range contain numbers or dates. (EX 161)

COUNTA function A function thattallies how many cells in specified range are not blank (contain numbers, dates, or text). (EX 161)

date function A function that inserts or calculates dates and times. (EX 166)

error value A message in a cell that indicates some part of a formula is preventing Excel from returning a calculated value. (EX 150)

exact match lookup A lookup where the lookup value must match one of the compare values in the lookup table. (EX 176)

fill handle A small black square in the lower-right corner of a selected cell or range that you drag over an adjacent cell or range to copy the original content and formatting into the selected range. (EX 162)

Goal SeekA feature that automates the trial-and-error method by allowing you to specify a value for the calculated item, which Excel uses to determine the input value needed to reach that goal. (EX 180)

IF function A logical function that tests a condition and then returns one value if the condition is true and another value if the condition is false. (EX 161)

logical function A function that works with statements that are either true or false. (EX 161)

lookup function A function that finds a value in a table of data and inserts it in another location in the worksheet, such as a cell or in a formula. (EX 176)

lookup tableA table that stores the data you want to retrieve with a lookup function in categories. (EX 160)

lookup value The category you want to find in a lookup table. (EX 160)

MAX function A function thatdisplays the maximum value in a range. (EX 132)

Median function A function thatdetermines the middle value in a range.(EX 132)

median The middle value in data. (EX 145)

MIN function Afunction thatdisplays the minimum value in the range. (EX 132)

mixed reference A cell reference thatcontains both relative and absolute references; for example, the mixed reference for cell D2 can have an absolute row (such as $D6) or an absolute column (such as D$6). (EX 154)

nest To place one thing inside another, such as a function inside another function. (EX 142)

optional argument An argument that is not required for the function to return a value. (EX 133)

relative reference A cell reference thatis interpreted in relation to the location of the cell containing the formula. (EX 133)

required argument An argument that is needed for the function to return a value. (EX 133)

return values The data values you want to retrieve from a lookup table. (EX 160)

significant digits The number of digits that should be displayed for a calculation; they indicate the accuracy of the measured and calculated values. (EX 140)

TODAY function A date function that displays the current date. (EX 167)

trial-and-error method A way to perform what-if analysis by changing one or more of the input values to a value you estimate to see how it impacts the calculated results. (EX 179)

what-if analysisA way to explore the impact that changing input values has on the calculated values in a workbook. (EX 179)

WORKDAY function A function that displays the date of a weekday that is specified number of weekdays past a starting date. (EX 161)

VLOOKUP function A function thatreturns values from a vertical lookup table. (EX 160)