CH 2: Formulas and Functions
Quiz yourself by thinking what should be in
each of the black spaces below before clicking
on it to display the answer.
Help!
|
|
||||
|---|---|---|---|---|---|
| Absolute cell reference | A designation that provides a permanent reference to a specific cell. When you copy a formula containing an absolute reference, the cell reference in the copied formula does not change, regardless of where you copy the formula. An absolute cell reference
🗑
|
||||
| Argument | A variable constant input, such as a cell reference or value, needed to complete a function. The entire group of arguments for a function is enclosed in parenthesis.
🗑
|
||||
| AVERAGE function | A statistical function that calculates the arithmetic mean, or average, of values in a range.
🗑
|
||||
| Breakpoint | The lowest value for a specific category or series in a lookup table.
🗑
|
||||
| Circular reference | A situation that occurs when a formula contains a direct or an indirect reference to the cell containing the formula.
🗑
|
||||
| Column index number | The number of the column in the lookup table that contains the return values. Used as the third argument in a VLOOKUP or HLOOKUP function.
🗑
|
||||
| COUNT function | A statistical function that tallies the number of cells in a range that contain values you can use in calculations, such as the numerical and date data, but excludes blank cells or text entries from the tally.
🗑
|
||||
| COUNTA function | A statistical function that tallies the number of cells in a range that are not blank; that is, cells that contain data, whether a value, text, or formula.
🗑
|
||||
| COUNTBLANK function | A statistical function that tallies the number of cells in a range that are blank.
🗑
|
||||
| Formula AutoComlete | A feature that displays a list of functions and defined names that match letters as you type a formula.
🗑
|
||||
| Function | A predefined computation that simplifies creating a complex calculation and produces a result based on inputs known as arguments.
🗑
|
||||
| Function ScreenTip | A small pop-up description that displays the arguments for a function as you enter it directly in a cell.
🗑
|
||||
| HLOOKUP function | A lookup & reference function that looks up a value in a horizontal lookup table where the first row contains the values to compare with the lookup value.
🗑
|
||||
| IF function | A logical function that evaluates a condition and returns one value if the condition is true and a different condition if the value is false.
🗑
|
||||
| Logical test | An expression that evaluates to true or false; the first argument in an IF function.
🗑
|
||||
| Lookup table | A range that contains data for the basis of the lookup and data to be retrieved. In a vertical lookup table, the first column contains a list of values to compare to the lookup value.
🗑
|
||||
| Lookup value | The cell reference of the cell that contains the value to lookup within a lookup table.
🗑
|
||||
| MAX function | A statistical function that finds the highest value in a range.
🗑
|
||||
| MEDIAN function | A statistical function that finds the midpoint value, which is the value that one half of the values in a list are above or below.
🗑
|
||||
| MIN function | A statistical function that finds the lowest value in a range.
🗑
|
||||
| Mixed cell reference | A designation that combines an absolute cell reference with a relative cell reference, such as $B4 or B$4.
🗑
|
||||
| Nested function | A function that contains another function embedded inside one or more of its arguments.
🗑
|
||||
| NOW function | A date & time function that uses the computer's clock to display the current date and time in a cell.
🗑
|
||||
| Nper | The number of payment periods over the life of a loan or investment; the second argument in the PMT function.
🗑
|
||||
| PMT function | A financial function in Excel that calculates the periodic loan payment.
🗑
|
||||
| Pv | The present value of a loan or an annuity; the third argument in the PMT function and refers to the original amount of the loan.
🗑
|
||||
| Quick Analysis | A tool that provides a fast way to analyze a selected range of data by inserting basic calculations, creating charts, converting the data to a table, or applying conditional formatting or other analytical features.
🗑
|
||||
| Range name | A word or string of characters assigned to one or more cells. It can be up to 255 letters, or numbers but must start with a letter or underscore and have no spaces or special symbols.
🗑
|
||||
| Rate | The periodic interest rate; the percentage of interest paid for each payment period; the first argument in the PMT function.
🗑
|
||||
| Relative cell reference | A designation that indicates a cell's relative location within the worksheet using the column letter and row number. When a formula with a relative cell reference is copied, the cell references in the copied formula change relative to the position of the
🗑
|
||||
| SUM function | A statistical function that calculates the total of values contained in two or more cells.
🗑
|
||||
| Syntax | The rules that dictate the structure and components required to perform the necessary calculations in an equation or to evaluate expressions.
🗑
|
||||
| Table array | The range that contains the body of lookup table, excluding column labels. The first column must be in ascending order to find a value in a range;it can be in any order to look up an exact value. The second argument within a VLOOKUP or HLOOKUP function.
🗑
|
||||
| TODAY function | A date & time function that displays the current date in a cell.
🗑
|
||||
| VLOOKUP function | A lookup & reference function that looks up a value and returns a related result from the lookup table.
🗑
|
||||
| A set of rules that governs the structure and components for properly entering a function. | Syntax
🗑
|
||||
| Displays the current date. | TODAY function.
🗑
|
||||
| Indicates a cell's specific location; the cell reference does not change when you copy the formula | Absolute cell reference.
🗑
|
||||
| Occurs when a formula directly or indirectly refers to itself. | Circular reference.
🗑
|
||||
| An input, such as a cell reference or value, needed to complete a function. | Argument.
🗑
|
||||
| Identifies the highest value in a range. | MAX function.
🗑
|
||||
| Tallies the number of cells in a range in a range that contains values. | COUNT function.
🗑
|
||||
| Looks up a value in a vertical lookup table and returns a related result from the lookup table. | VLOOKUP function.
🗑
|
||||
| A range that contains data for the basis of the lookup and data to be retrieved. | Lookup table
🗑
|
||||
| Calculates the arithmetic mean, or average, of values in a range. | AVERAGE function.
🗑
|
||||
| Identifies the midpoint value in a set of values. | MEDIAN function.
🗑
|
||||
| Displays the current date and time. | NOW function.
🗑
|
||||
| Evaluates a condition and returns one value if the condition is true and a different value if the condition is false. | IF function.
🗑
|
||||
| Calculates the total of values contained in two or more cells. | SUM function.
🗑
|
||||
| Calculates the periodic payment for a loan with a fixed interest rate and fixed term. | PMT function.
🗑
|
||||
| Indicates a cell's location from the cell containing the formula; the cell reference changes when the formula is copied. | Relative cell reference.
🗑
|
||||
| Contains both an absolute and a relative cell reference in a formula; the absolute part does not change but the relative part does when you copy the formula. | Mixed cell reference.
🗑
|
||||
| A word or sting of characters that represents one or more cells. | Range name.
🗑
|
||||
| An expression that evaluates to true or false. | Logical test.
🗑
|
||||
| Displays the lowest value in a range. | MIN function.
🗑
|
||||
| If cell D15 contains the formula =$C$5*D$15, what is the D15 in the formula? | Circular reference.
🗑
|
||||
| What function would most appropriately accomplish the same thing as =(B5+C5+D5+E5+F5)/5? | =AVERAGE(B5:F5)
🗑
|
||||
| When you start =AV, what displays a list of functions and defined names? | Formula AutoComplete
🗑
|
||||
| A formula containing the entry =$B3 is copied to a cell one column to the right and two rows down. How will the entry appear inits new location? | =$B5
🗑
|
||||
| Cell B10 contains a date, such as 1/1/2016. Which formula will determine how many days are between that date and the current date, given that the cell containing the formula is formatted with Number Format? | =TODAY()-B10
🗑
|
||||
| Given that cells A1, A2, and A3 contain values 2, 3, and 10, respectively, and B6, C6, and D6 contain values 10, 20, and 30, respectively, what value will be returned by the function =IF(B6>A3,C6*A1,D6*A2) | 90
🗑
|
||||
| Given the function =VLOOKUP(C6,$D$12:$F$18,3), the entries in | Range D12:D18 are in ascending order.
🗑
|
||||
| The function =PMT(C5,C7,-C3) is stored in cell C15. What must be stored in cell C5? | Periodic interest rate.
🗑
|
||||
| Which of the following is not an appropriate use of the SUM function? | =SUM(D15-C15)
🗑
|
||||
| Which of the following is not an acceptable range name? | Goal for 2016
🗑
|
||||
| Semi-selection | The process of using the mouse pointer to select cells while building a formula.
🗑
|
Review the information in the table. When you are ready to quiz yourself you can hide individual columns or the entire table. Then you can click on the empty cells to reveal the answer. Try to recall what will be displayed before clicking the empty cell.
To hide a column, click on the column name.
To hide the entire table, click on the "Hide All" button.
You may also shuffle the rows of the table by clicking on the "Shuffle" button.
Or sort by any of the columns using the down arrow next to any column heading.
If you know all the data on any row, you can temporarily remove it by tapping the trash can to the right of the row.
To hide a column, click on the column name.
To hide the entire table, click on the "Hide All" button.
You may also shuffle the rows of the table by clicking on the "Shuffle" button.
Or sort by any of the columns using the down arrow next to any column heading.
If you know all the data on any row, you can temporarily remove it by tapping the trash can to the right of the row.
Embed Code - If you would like this activity on your web page, copy the script below and paste it into your web page.
Normal Size Small Size show me how
Normal Size Small Size show me how
Created by:
TroyIS2241
Popular Computers sets