Chapter 4: Use Financial and Lookup Functions, Define Names, and Validate Data

Rate

In the Excel PMT function, the term used to indicate the interest rate for a loan.

Nper

The abbreviation for number of time periods in various Excel functions.

Interest

The amount charged for the use of borrowed money. Lookup functions A group of Excel functions that look up a value in a defined range of cells located in another part of the workbook to find a corresponding value.

Data table (Excel)

A range of cells that shows how changing certain values in your formulas affect the results of those formulas and that makes it easy to calculate multiple versions in one operation.

Data validation (Excel)

A technique by which you can control the type of data or the values that are entered into a cell by limiting the acceptable values to a defined list.

Name

A word or string of characters in Excel that represents a cell, a range of cells, a formula, or a constant value; also referred to as a defined name.

Defined name

A word or string of characters in Excel that represents a cell, a range of cells, a formula, or a constant value; also referred to as simply a name.

One-variable data table

A data table that changes the value in only one cell.

Two-variable data table

A data table that changes the values in two cells.

Table arrayb

A defined range of cells, arranged in a column or a row, used in a VLOOKUP or HLOOKUP function.

Validation list (Excel)

A list of values that are acceptable for a group of cells; only values on the list are valid and any value not on the list is considered invalid.

PMT function

An Excel function that calculates the payment for a loan based on constant payments and at a constant interest rate.

HLOOKUP

An Excel function that looks up values that are displayed horizontally in a row.

VLOOKUP

An Excel function that looks up values that are displayed vertically in a column. What-if analysis The process of changing the values in cells to see how those changes affect the outcome of formulas in the worksheet.

Type argument

An optional argument in the PMT function that assumes that the payment will be made at the end of each time period.

Principal

Another term for present value.

Goal Seek

One of Excel's What-If Analysis tools that provides a method to find a specific value for a cell by adjusting the value of one other cell—find the right input when you know the result you want.

Financial functions

Pre-built formulas that perform common business calculations such as calculating a loan payment on a vehicle or calculating how much to save each month to buy something; financial functions commonly involve a period of time such as months or years.

Scope

The location within which a defined name is recognized without qualification—usually either to a specific worksheet or to the entire workbook.

Present value (Pv)

The total amount that a series of future payments is worth now; also known as the principal.

Future value (Fv)

The value at the end of the time periods in an Excel function; the cash balance you want to attain after the last payment is made—usually zero for loans.

Arguments

The values that an Excel function uses to perform calculations or operations.