Excel - Ch 2

Question 1

Indicates a cell’s relative location from original cell; when you copy formula, cells referenced in formula change, but maintain the same relative difference (A12)

Answer 1

Relative Cell Reference

Question 2

Provides constant reference to specific cell; cell reference does not change when copied; $ before Row & Column; used when there is a constant/fixed amount in formula ($A$12)

Answer 2

Absolute Cell Reference

Question 3

Combines both types of references; $ in front of Column OR Row to denote which one is fixed; (A$12) or ($A12)

Answer 3

Mixed Cell Reference

Question 4

Predefined computation that simplifies creating a formula that performs a complex calculation; Excel has 400+ types in 14 different categories

Answer 4

Function

Question 5

FUCTION : Provides method for manipulating date & time status

Answer 5

Date & Time

=DATE

Question 6

FUNCTION : Performs financial calculations (payments, rates, present/future value)

Answer 6

Financial

=RATE ; =PV ; =FV

Question 7

FUNCTION : Performs logical tests & returns value of the test (if, and, or, not)

Answer 7

Logical

=IF ; =AND ; =OR

Question 8

FUNCTION : Looks up values, creates links to cells or provides references to cells in a worksheet

Answer 8

Lookup & Reference

=VLOOKUP ; =HLOOKUP

Question 9

FUNCTION : Performs common statistical calculations (sums, averages, median)

Answer 9

Statistical

=SUMS ; =AVERAGE ; =MEDIAN

Question 10

Rules that indicate the structure & components required to perform the necessary calculations; function started by = followed by type, followed by arguments in enclosed in brackets

Answer 10

Syntax

Question 11

Specify the inputs (such as cells, values, or arithmetic expressions) that are required to complete the formula; in some cases, multiple are required followed by commas

Answer 11

Argument

Question 12

Displays list of functions & defined names that match letters as you type

Answer 12

Formula AutoComplete

Question 13

Small pop-up description that displays function’s arguments; shows up after you type function name and opening bracket

Answer 13

Function ScreenTip

Question 14

Totals values 1 one or more cells; more efficient than adding each cell

Answer 14

SUM Function

Question 15

Finds midpoint value in a range; if uneven number of cells, the average is taken from the 2 mid points

Answer 15

Median Function

Question 16

Calculates arithmetic average of values between range of cells

Answer 16

Average Function

Question 17

Analyzes an argument list to determine lowest value

Answer 17

MIN Function

Question 18

Analyzes an argument list to determine highest value

Answer 18

MAX Function

Question 19

Tallies number of cells in a range that contain values you can use

Answer 19

COUNT Function

Question 20

Tallies number of cells in a range that are blank

Answer 20

COUNTBLANK Function

Question 21

Tallies all cells in a range except if it is blank (all values & text)

Answer 21

COUNTA Function

Question 22

Occurs when there is a function inside another function

Answer 22

Nested Function

Question 23

Set of analytical tools you can use to apply formatting, create charts/tables, and insert basic functions (shows at bottom right of a selected range)

Answer 23

Quick Analysis

Question 24

Displays current date in a cell; updates when document is opened or saved

Answer 24

TODAY Function

Question 25

Uses computer clock to display current date/time; updates when a documents is opened or saved

Answer 25

NOW Function

Question 26

Tests specified criteria to see if it is true or false, then returns one value when condition is met (true) or another value when condition is not met (false)

Answer 26

IF Function

=IF(logical_test, [value_if_true], [value_if_false])

Question 27

Comparison Operator : < >

Answer 27

Less than / Greater than

Question 28

Comparison Operator : <= =>

Answer 28

Less than or Equal to / Greater than or Equal to

Question 29

Contains either a value or an expression that evaluates to TRUE or FALSE; requires comparison between at least 2 variables (different cells)

Answer 29

Logical Test

Question 30

Accepts a value and looks for value in left column of a table array, returns for another located in same row from a specified column

Answer 30

VLOOKUP Function

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Question 31

Cell reference of the cell that contains the value to look up

Answer 31

Lookup Value

Question 32

Range that contains the lookup (input) table

Answer 32

Table Array

Question 33

Column number in the lookup table than contains the return values

Answer 33

Column Index Number

Question 34

Determines how the VLOOKUP Function handles lookup values that are not an exact match for the data in the lookup table; OPTIONAL

Answer 34

Range Lookup

Question 35

Range containing table of values & text from which data can be retrieved; should contain at least 2 rows and 2 columns (not including headings)

Answer 35

Lookup Table

Question 36

Lowest value in a category or series

Answer 36

Breakpoint

Question 37

Horizontal Lookup Table

Answer 37

HLOOKUP

=HLOOKUP(lookup_value, _table_array, row_index_num, [range_lookup])

Question 38

Calculates payments for a loan for a fixed amount at a fixed periodic rate for a fixed time

Answer 38

PMT Function

=PMT(rate, nper, ps, [fv], [type])

Question 39

Interest rate per period (if annual % is 12%, each month would be 1%)

Answer 39

Rate

Question 40

Total number of payment periods (monthly, weekly, annually, etc)

Answer 40

Nper

Question 41

Present Value of the loan

Answer 41

Pv