The fill handle is a tiny black square that appears in the lower-right corner of the selected cell(s). To use the fill handle to copy to adjacent cells, you

The AutoFill capability is a wonderful shortcut and the fastest way to enter certain series into adjacent cells. In essence, you enter the first value(s) of a series, and then drag the fill handle to the adjacent cells that are to contain the remaining values in that series. Excel creates the series for you based on the initial value(s) you supply.

Calculates the payment for a loan based on constant payments and a constant interest rate.

Syntax

Rate is the interest rate for the loan.

Nper is the total number of payments for the loan.

Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be zero, that is, the future value of a loan is 0.

Type is the number 0 (zero) or 1 and indicates when payments are due.

Set type equal to                     If payments are due

0 or omitted                                 At the end of the period

1                                                 At the beginning of the period

Remarks

Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for nper.

Tip To find the total amount paid over the duration of the loan, multiply the returned PMT value by nper.

The Goal Seek command enables you to set an end result in order to determine the input to produce that result. Only one input can be varied at a time. Not every problem has a solution, in which case Excel returns an appropriate message.

Counts the number of cells that contain numbers and numbers within the list of arguments. Use COUNT to get the number of entries in a number field in a range or array of numbers.

Value1, value2, ... are 1 to 30 arguments that can contain or refer to a variety of different types of data, but only numbers are counted.

Arguments that are numbers, dates, or text representations of numbers are counted; arguments that are error values or text that cannot be translated into numbers are ignored.

If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored. If you need to count logical values, text, or error values, use the COUNTA function.

Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.

Syntax

Value1, value2, ... are 1 to 30 arguments representing the values you want to count. In this case, a value is any type of information, including empty text ("") but not including empty cells. If an argument is an array or reference, empty cells within the array or reference are ignored. If you do not need to count logical values, text, or error values, use the COUNT function.

Returns the largest value in a set of values.

Syntax

Number1,number2,... are 1 to 30 numbers for which you want to find the maximum value.

Remarks

You can specify arguments that are numbers, empty cells, logical values, or text representations of numbers. Arguments that are error values or text that cannot be translated into numbers cause errors.

If an argument is an array or reference, only numbers in that array or reference are used. Empty cells, logical values, or text in the array or reference are ignored. If logical values and text must not be ignored, use MAXA instead.

If the arguments contain no numbers, MAX returns 0 (zero).

Returns the smallest number in a set of values.

Syntax

Number1, number2,... are 1 to 30 numbers for which you want to find the minimum value.

Remarks

You can specify arguments that are numbers, empty cells, logical values, or text representations of numbers. Arguments that are error values or text that cannot be translated into numbers cause errors.

If an argument is an array or reference, only numbers in that array or reference are used. Empty cells, logical values, or text in the array or reference are ignored. If logical values and text should not be ignored, use MINA instead.

If the arguments contain no numbers, MIN returns 0.

Returns the average (arithmetic mean) of the arguments.

Syntax

Number1, number2, ... are 1 to 30 numeric arguments for which you want the average.

Remarks

The arguments must be numbers or names, arrays, or references that contain numbers.

If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

Tip: When averaging cells, keep in mind the difference between empty cells and those containing the value zero, especially if you have cleared the Zero values check box on the View tab (Options command, Tools menu). Empty cells are not counted, but zero values are.

Adds all the numbers in a range of cells.

Syntax

Number1, number2, ... are 1 to 30 arguments for which you want the total value or sum.

Numbers, logical values, and text representations of numbers that you type directly into the list of arguments are counted. See the first and second examples following.

If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored. See the third example following.

Arguments that are error values or text that cannot be translated into numbers cause errors.

Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.

Syntax

VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

Lookup_value   is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table_array   is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.

The values in the first column of table_array can be text, numbers, or logical values.

Uppercase and lowercase texts are equivalent.

Col_index_num   is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup   is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

Remarks

Instead of copying entire cells, you can copy specified contents from the cells — for example, you can copy the resulting value of a formula without copying the formula itself. To achieve this:

1. Select the cells you want to copy.

2. Perform the Copy action.

3. Select the upper-left cell of the paste area.

4. On the Edit menu, click Paste Special.

5. Click an option under Paste, and then click OK.

The name cannot include the space character.

You can define more than one name for a single cell.

You can use the name for a cell in the formulas as a reference.

You can use the Insert Menu Þ Name Þ Define alternatively.

On the Window menu, click Freeze Panes.

To restore a window split into two scrollable areas, double-click any part of the split bar that divides the panes.

To remove unscrolling "frozen" panes, click Unfreeze Panes on the Window menu.

You can rearrange the rows or columns of a list based on the values in the list by sorting. When you sort, Microsoft Excel rearranges rows, columns, or individual cells by using the sort order that you specify. You can sort lists in ascending (1 to 9, A to Z) or descending (9 to 1, Z to A) order, and sort based on the contents of one or more columns.

Microsoft Excel sorts lists alphabetically by default. If you need to sort months and weekdays according to their calendar order instead of their alphabetic order, use a custom sort order. You can also rearrange lists in a specific order by creating custom sort orders. For example, if you have a list that contains the entry "Low," "Medium," or "High" in a column, you can create a sort order that arranges rows that contain "Low" first, rows that contain "Medium" next, and rows with "High" last.

Excel Week 3 Class work file