Microsoft Excel Tutorial 3 Key Terms

relative reference

a cell reference that is interpreted in relation to the location of the cell containing the formula; M23 is a relative reference to cell M23

absolute reference

a cell reference that remains fixed when the cell formula is copied to a new location. It includes a $ in front of the column letter and row number; $M$23 is the absolute reference to cell M23

logical function

a function that works with statements that are either true or false

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

constant

a value in a formula that doesn't change

SUM function

adds the values in the range

fill handle

appears in the lower-right corner of the selection after you select a range

Goal Seek

automates the trial-and-error process by allowing you to specify a value for a calculated item, which Excel uses to determine the input value needed to reach that goal; Goal seek starts by setting the calculated value and works backward to determine the correct input value; Goal Seek can only be used with calculated numbers, not text or a value from a lookup table.

nested

when functions are placed inside another function

exact match lookup

when the lookup value must match one of the compare values in the first column of the lookup table

Insert Function

button that opens the Insert Function dialog box from which you can select a function

AVERAGE function

calculates the average value of the range

mixed reference

contains both relative and absolute references; a mixed reference for cell A2 can either be $A2 or A$2

WORKDAY function

displays the date of a weekday that is a specified number of weekdays past a starting date

MIN function

displays the minimum value in the range

TODAY function

function used to display the current date in a worksheet; has the syntax =TODAY() ; although the TODAY function doesn't have any arguments, you still must include the parentheses for the function to work. The date displayed by the TODAY function is updated automatically whenever you reopen the workbook or enter a new calculation

what-if-analysis

lets you explore the impact that changing input values has on the calculated values in the workbook

Required arguments

needed for the function to return a value; displayed in bold

Optional arguments

not required for the function to return a value

approximate match lookup

occurs when the lookup value falls within a range of numbers in the first column of the lookup table

median

provides the middle value from a data sample

trial-and-error method

requires some guesswork as you estimate which values to change and by how much

VLOOKUP function

returns values from a vertical lookup table by specifying the lookup value to match to a compare value, the location of the lookup table, and the column in the table that contains the return values

lookup table

stores the data you want to retrieve in categories

COUNTA function

tallies how many cells in the specified range are not blank (contain numbers, dates, or text).

COUNT function

tallies how many cells in the specified range contain numbers or dates

Compare values

the categories located in the first column of the lookup table and are used for matching to a lookup value specified by the user

lookup value

the category you want to find in a lookup table

Return values

the data values you want to retrieve from the look up table and are located in the second and subsequent columns

Arguments

the numbers, text, or cell references used by the function to return a value

Lookup functions

find values in tables of data and insert them in another location in the worksheet such as cells or in formulas

significant digits

indicate the accuracy of the measured and calculated values; digits with specifications as to how many should be displayed with any calculation

error value

indicates that some part of a formula is preventing Excel from returning a calculated value; begins with a pound sign (#) followed by an error name that indicates the type of error

Date functions

insert or calculate dates and times; particularly useful in business workbooks that involve production schedules and calendar applications

AutoFill

1) provides a quick way to enter content and formatting in cells based on existing entries in adjacent cells 2) AutoFill can be used to create a series of numbers, dates, or text based on a pattern

MEDIAN function

determines the middle value in the range