Chapter 4 Excel

rules for defining names

*first character must be a letter, underscore, or back-flash(/) *after first character, the remaining can be letters, numbers,periods, and underscore characters. *the single letter C or R (either upper or lowercase) cannot be defined as a name because excel uses them for selecting a row or colunm *cannot be longer than 255 characters *cannot be the same as a cell reference (e.g. M$10) *names can contain upper or lowercase letters, however excel does not distinguish between them so you cannot create the same name in uppercase if you already have it in lowercase; excel considers them to be the same.

error checking command

a command that checks for common errors that occur in formulas.

one-variable data table

a data table that changes the value in only one cell. Use one-variable data table if you want to see how different interest rates affect a monthly payment.

table of array

a defined range of cells, arranged in a column or a row, used in a vlookup or hlookup function. In the lookup formula, the defined range is referred to as the table of array.

lookup functions

a group of excel functions that lookup a value in a defined range of cells located in another part of the workbook to find a corresponding value. The lookup function looks up values in either a one-row or a one-column range.

validation list

a list of values that are acceptable for a group of cells, only values of the lists are valid and any value not in the list is considered invalid.

data table

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. Makes it easy easy to calculate multiple versions in one operation, and then view and compare the results of all the different variations. By using a data table, you can calculate the possible values for each argument.

error value

a result of a formula that Excel cannot evaluate correctly.

data validation

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. Technique for improving accuracy when completing a worksheet. It improves accuracy because it limits and controls the type of data an individual, such as a order taker, can enter into a form.

trace error command

a tool that helps locate an resolve an error by tracing the selected error value. Another tool you can use to help locate and resolve an error.

defined name

a word of 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. When creating a formula, a defined name may be used instead of the cell reference. The advantage of naming a range of cells is that you can use the name in a formula in other parts of your workbook.

precedent cells

cells that are referred to by a formula in another cell.

dependent cells

cells that contain formulas that refer to other cells.

two-variable data table

changes the values in two cells. Use if you want to see how different interest rates and different payments periods will affect a monthly payment.

green traingles

display in the top left corners of cells ndicating a potential error.

PMT function

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

lookup

excel function that looks up values either in a one-row or one-column range. In the lookup formula, the defined range is referred to as the table of array.

financial functions

pre-built formulas that perform common business calculations (e.g. loan payment, savings). Commonly involve a period of time such as months or years.

goal seek

provides a method to find a specific value for a cell by adjusting the value of another cell; find the right input when you know the result you want.

scope of name

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

principal

the total amount that a series of future payments is worth know; also known as present value.

formula auditing

tools and commands accessible from the formulas tab that help you check for errors. In complex worksheets, use these features to show relationships between cells and formulas, to ensure that formulas are logical and correct, and to resolve error messages.

future value

value after last payment is made; for loans usually is 0.

excel error values

### - cannot see data #DIV/0 - cannot divide by 01 #NAME? - does not recognize a name you used in a formula #VALUE - cannot use a text field in a formula #REF - cannot locate a reference #N/A - no value is available #NUM- invalid argument in a worksheet formula\

nper

number of time periods