intermediate excel formulas by gracialova

VIEWS: 49 PAGES: 23

More Info
									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

								
To top