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