Excel 3

Contains a number divided by zero

#DIV/O!

A value is not available to a function or formula

#N/A

A formula or function requires two cell ranges to intersect, but they don't

#NULL!

Invalid numbers are used in a formula or function

#NUM!

Excel doesn't recognize text in the formula or function

#Name?

A cell reference used in a formula or function is no longer valid

#REF!

The wrong type of argument is used in a function or formula

#Value!

AutoFilling

A quick way to enter content and format cells based on existing entries in adjacent cells

Cell reference that is fixed; want copied formulas to always *refer to the same cell* if there is a constant

Absolute Reference?

SUM function

Adds the values in a range

Fill Handle

Appears after you select a range in order to copy a format or formula

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

Average function =AVERAGE (number 1, number 2) Number 1 is required

Calculates the average of the range

Compare Values

Categories located in the first column of the look up table Used for matching to a lookup value

Look up Value

Category you want to find in a look up table

Absolute reference

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 M23.

Relative reference

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

Relative References

Cell reference will change when the formula is copied to a new location

Mixed Reference

Contains both relative and absolute references

Return Values

Data values you want to retrieve from the lookup table and are located in the second columns

Median Function

Determines the middle values

Min function

Determines the minimum value

TODAY function

Display the current date on a worksheet

Function Arguments Dialogue Box

Displays the arguments used by each function (required arguments are in bold, optional are in normal type) Select a range

WORKDAY function

Displays the date of a weekday that is a specific number of weekdays past a starting date

INT Function INT (number)

Displays the integer portion of a number

Look Up functions

Find values in tables of data and insert them in another location in the worksheet

Absolute Reference

Fixed reference; always references the same cell no matter where it is moved Cell reference will not change EX: $A$4

Logical function

Function that works with statements that are either T or F; returns a different value if it T or F

Nested

Functions can be placed inside another function EX: =ROUND (AVERAGE(A1:A100),2) first average, rounds

Quick Analysis Tool

Generate columns and rows of summary statistics that can be used to analyze data TOTALS section

Series Dialogue Box

HOME tab, editing group, Fill

Significant Digits

How many digits should be displayed with each calculation; should the same for input and calculated values

Cell Styles

Identify notes, input values, calculated values; way of marking different types of values

What if analysis

Impact that changing input values has on the calculated values in the workbook

Error Value

Indicates some part of a formula is preventing Excel from returning a calculated value

Date Functions

Insert or calculate dates and times

IF Function

Logical function that tests a condition and returns one value if a condition is T and a different value if it is F

Values of a formula or function can be found at the intersection of rows and columns; running total

Mixed References?

Required Arguments

Needed for the function to return a value, displayed in bold

Optional requirements

Not required for the function to return a value

Arguments

Numbers, text, cell references used by 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 look up table

Functions

Organized by category in the FORMULA's tab, Function Library Group

Functions

Provide a way to quickly calculate summary data (total, average, median) Categories: Cube, Database, Date and Time, Engineering, Finance, Information, Lookup and Reference, Statistical, Math/Trig

Cell reference that changes; apply the same formula with input cells that share a *common layout or pattern* Used when copying a formula that calculates summary stats across columns or rows of data values

Relative Reference?

Trail and error method

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

VLOOKUP function

Returns values from a vertical look up table by specifying a. the lookup value to match to a compare value b. the location of the look up table c. the column in the table that has the returned values FORMULA LOOK-UP and REFERENCE

Round Function (number, num_digits)

Rounds a number to the number of digits specified in num_digits

Lookup table

Stores the data you want to retrieve in categories

Comparison Operator

Symbol that indicates the relationship between two values

COUNTA function

Tallies how many cells in the specified range are not blank

COUNT function

Tallies how many cells in the specified range that contains numbers or dates

Insert Function button

This button opens an Insert Function dialog box where you can select a function (Fx) PROCESS search for a function (type what you want) select a function (select one) ok (take you to function arguments)

Constant

Value in a formula that does not change

Exact Match look up

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