VIEWS: 49 PAGES: 23 CATEGORY: Software POSTED ON: 7/30/2012 Public Domain
June, 2007 1 2 Excel Intermediate Formulas Table of Contents What is a Formula ....................................................................................................................5 Constructing formulas ..............................................................................................................5 Enter a formula .........................................................................................................................6 Calculate the running balance..............................................................................................6 Calculating a value based on a condition ................................................................................8 The IF worksheet function ...................................................................................................8 Number formats ................................................................................................................. 11 Amortize................................................................................................................................. 11 Create a mortgage term comparison ................................................................................ 11 3-D Relationships .................................................................................................................. 14 Refer to the same cell or range on multiple sheets by using a 3-D reference ............... 15 Use functions to create a grade book ................................................................................... 17 V and H Lookup .....................................................................................................................19 3 4 Excel Intermediate Formulas What is a Formula A formula is an equation that performs operations on worksheet data. Formulas can perform mathematical operations, such as addition and multiplication, or they can compare worksheet values or join text. Formulas can refer to other cells on the same worksheet, cells on other sheets in the same workbook, or cells on sheets in other workbooks. Using formulas and functions to perform calculations on cell data is one of the fundamental features of Excel. You will be able to calculate the results for any number of cells at once by making calculations directly in the worksheet and Excel has automatic recall. If you change a number in the column, the total automatically reflects the change. This eliminates the need for paper and pencil calculations or a calculator. Operators: + Addition - Subtraction * Multiplication / Division ˆ Exponentiation Remember your algebraic hierarchy. Formulas will perform anything in Parentheses first, then Exponentiation, Multiplication, Division, Addition and last Subtraction. Constructing formulas The order of the elements in a formula determine the final result of the calculation. Formulas in Microsoft Excel follow a specific syntax, or order, that includes an equal sign (=) followed by the elements to be calculated (the operands), which are separated by calculation operators (symbols used in formulas to perform mathematical functions). Each operand can be a value that does not change (a constant value), a cell or range reference, a label, a name, or a worksheet function. Excel performs the operations from left to right — according to the order of operator precedence — starting with the equal sign (=). You can control the order of calculation by using parentheses to group operations that should be performed first. For example, the following formula produces 11 because Excel calculates multiplication before addition. 5 The formula multiplies 2 by 3 and then adds 5 to the result. =5+2*3 In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then multiplies the result by 3 to produce 21. =(5+2)*3 Enter a formula 1. Click the cell in which you want to enter the formula. Type = (an equal sign). If you click Paste Function , Microsoft Excel inserts an equal sign for you. 2. Enter the formula. 3. Press ENTER. Tips You can enter the same formula into a range of cells by selecting the range first, typing the formula, and then pressing CTRL+ENTER. You can also enter a formula into a range of cells by copying a formula from another cell. Calculate the running balance You can create a checkbook register in Excel to track your banking transactions. As part of the spreadsheet, you can build a formula to calculate your running balance. In this example, assume that cell F6 contains the previous balance, cell D7 contains the first transaction's deposit subtotal, and cell E7 contains any cash-received amount. To calculate the current balance for the first transaction, enter the following formula in cell F7: =SUM(F6,D7,–E7) As you enter new transactions, copy this formula into the current balance cell for each new transaction. 6 Create this worksheet: 7 Calculating a value based on a condition A formula in Microsoft Excel performs calculations on values in your worksheet. Typically, formulas perform calculations on all the values in a given range. However, what if you want Excel to change your formula if a certain condition is true, or what if you want to include only the values that meet certain conditions in the calculation? For example, you might want to track the orders placed by your salespeople and then summarize the sales for each salesperson without reorganizing your data. Or you might want to determine the bonus amount awarded for each sale, based on the total invoice amount. When you want formulas to perform conditional tests, you can use conditional formulas in Excel. The IF worksheet function Suppose that your company determines sales bonuses on a sliding scale, awarding either 10 percent or 15 percent, based on the invoice amount. To determine which one of two values to use, based on a condition that is either true or false, use the IF worksheet function. The IF worksheet function returns a bonus of either 10% or 15%, based on the invoice amount. The IF worksheet function checks a condition that must be either true or false. If the condition is true, the function returns one value; if the condition is false, the function returns another value. The function has three arguments: the condition you want to check, the value to return if the condition is true, and the value to return if the condition is false. You can use the “IF” function to omit the “trailing” balance that occurs when using a running balance formula (checkbook). The examples below relate to the running balance spreadsheet you just created. Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression Pa evaluates to 8 FALSE. This argument can use any comparison calculation operator. Logical_test example – If the Debit cell and the Credit Cell=0 B5+C5=0 Value_if_true is the value that is returned if logical_test is TRUE. For example, if this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, then the IF function displays the text "Within budget". If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula. Value_if_true example – Leave cell blank “” Value_if_false is the value that is returned if logical_test is FALSE. For example, if this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, then the IF function displays the text "Over budget". If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. Value_if_false can be another formula. Value_if_false example – Subtract the debit from the previous balance add the credit and enter the sum. D4-B5+C5 Completed formula = IF(B5+C5=O,””,D4-B5+C5) 9 Change your spreadsheet Change your running balance spreadsheet to omit the “trailing” balance. 10 Number formats In Microsoft Excel, you can use number formats to change the appearance of numbers, including dates and times, without changing the number behind the appearance. The number format you apply does not affect the actual cell value — displayed in the formula bar — that Excel uses to perform calculations. The General number format The General format is the default number format. For the most part, what you enter in a cell that is formatted with the General format is what is displayed. However, if the cell is not wide enough to show the entire number, the General format rounds numbers with decimals and uses scientific notation for large numbers. General format cells have no specific number format (no decimal points). Number is used for general display of numbers (decimal points, but no dollar signs). Currency and Accounting offer specialized formatting for monetary value. Currency formats are used for general monetary values (negative numbers shown as -$10.00). Accounting formats line up the currency symbols and decimal points in a column (negative numbers shown as ($10.00). Amortize Create a mortgage term comparison Create a spreadsheet to calculate your mortgage payment and/or to display a comparison, using the “PMT” function. The “PMT” function calculates the payment for a loan based on constant payments and a constant interest rate. 11 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 0 (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. Rate 7.50% divided by 12. We are looking for the interest rate for one month. Nper 30 times 12. We need to determine the total number of payments. Pv -$100,000.00. The current value of our future payments is a negative number. 12 Calculation for the monthly payment The monthly payment PMT is equal to = (the annual interest rate, divided by / 12 (12 months in the year), term length of the loan (the number of years) times * 12, negative - Principal. =PMT(C6/12,C7*12,-C5) Your F4 key will change your cell reference from relative to absolute. Example: C11 vs $C$11 13 Create an amortization schedule Set up the basic information as shown below 1. In cell F2, type =B2 and press Enter. 2. In cell G2, type = F2*($B$4)/12 and press Enter. 3. In cell H2, type =$B$6-G2 and press Enter. 4. In cell I2, type =F2-H2 and press Enter. 5. In cell F3, type=I2 and press Enter. 6. Select cell F3, and double-click the Auto Fill handle. (Your dates must be entered in the Month column). 7. Ditto G2, H2 & I2 8. Verify that your ending balance is the balloon payment you specified in B3. See formulas on next page. NOTE: To amortize without a balloon payment, the amount in B3 should be zero. 14 3-D Relationships You can use 3-D references to refer to cells on other sheets, to define names, and to create formulas by using various functions, such as: SUM - Adds all the numbers in a range of cells. AVERAGE - Returns the average (arithmetic mean) of the arguments. COUNT - 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. MAX - Returns the largest value in a set of values. MIN - Returns the smallest number in a set of values. Refer to the same cell or range on multiple sheets by using a 3-D reference The workbook must contain more than one worksheet. 1. Click the cell where you want to enter the function. 15 2. Type = (an equal sign), enter the name of the function, and then type an opening parenthesis. 3. Click the tab for the first worksheet to be referenced. 4. Hold down SHIFT and click the tab for the last worksheet to be referenced. 5. Select the cell or range of cells to be referenced. 6. Complete the formula. Create the following 3-D Relationship Worksheets 16 Use functions to create a grade book AVERAGE Used to determine the average of selected cells. 1. Click in the cell in which you want the average to be displayed. 2. Type an equal sign, the word average and the opening parenthesis. = average( 3. Click in the first cell of the range to be averaged. 4. Drag through remaining cells to be averaged. 5. Press Enter. MAX This function work similarly to AVERAGE. It is used to display the maximum value in the specified cells or range of cells. 1. Click in the cell in which you want the maximum value to be displayed. 2. Type = to begin the formula. 3. Click on in the Formula bar. 4. Select MAX. MIN This function also work similarly to AVERAGE. It is used to display the minimum value in the specified cells or range of cells. 1. Click in the cell in which you want the minimum value to be displayed. 2. Type = to begin the formula. 3. Click on in the Formula bar. 4. Select MIN. (see above figure). 17 SUM Adds all the numbers in a range of cells. 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. Examples SUM(3, 2) equals 5 SUM("3", 2, TRUE) equals 6 because the text values are translated into numbers, and the logical value TRUE is translated into the number 1. COUNT 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. Examples In this example: COUNT (A1:A7) equals 3. (Only numbers and dates are counted). COUNT (A4:A7) equals 2. 18 VLOOKUP 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. By default, the table must be sorted in an ascending order. Lookup_value is the value to be found in the first column of the array and 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 text are equivalent. Col_index_num is the column number in table_array from which the matching value must be returned. The first column of values in a table is column 1. 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. 19 Enter the Following Worksheet Data In B14 enter in 1.2 In D14 enter in a VLookup formula =vlookup(b14,a2:b12,2) Enter the following Worksheet Data In B6 enter the formula =B4*B5 to find what you would pay without a discount. In B7 enter a HLookup formula =hlookup(B5,B1:E2,2)*B6 to find out what your discount is. In B8 enter the formula =B6-B7 to find out how much you would pay with a discount. 20 BRINGING IT ALL TOGETHER Create this spreadsheet as a Grade Book 21 22 23