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
