Microsoft Excel Whiz 2002 2003

Lesson 4: Using Other Functions

Using Date Functions (NOW and DATE)

Using NOW

You may use today's date in a worksheet if, for example you want to find the number of days since your friend sent an e-mail to you. The formula would be today's date minus the e-mail date.

If you want to calculate the current date and time (that is your system clock), use =NOW(). If you want to include just the date, use =TODAY(). You do not include any arguments in the parenthesis.

Using Date

The Date function requires three integer arguments (that is, Year, Month, and Day). If the number is not formatted, it will look strange. However, when formatted as a date it can look more meaningful.

To enter Date, follow these steps:

1. Select the cell where you want the Date to appear.

2. Click the Insert Function button.

3. From the Function Category, choose Date & Time, then select the Date function.

4. Click OK. The Function Arguments appears.

5. Complete the Arguments.

6. Click OK.

Use Financial Functions (FV and PMT)

The financial functions help make decisions regarding borrowing and lending money. It deals with interest, rate, and time.

The following are the common arguments that you will see in the financial functions:

• Rate - is the interest rate per period. Usually, payments are calculated monthly, so annual rate would be divided by 12.

• Nper - is the total number of payment periods in an annuity. Monthly payments are usually dealt with and the term is often expressed in years, you multiply years and times it to 12.

• PMT - is the payment made each period; it cannot change over the life of the annuity. Typically, PMT contains principal and interest but no other fees or taxes. If PMT is omitted, you must include the PV payment.

• PV - is the present value, or the lump-sum amount that a series of future payments is worth right now. If PV is omitted, it is assumed to be zero (0), and you must include the PMT argument.

• FV - is the future value of the investment after the term is up at the interest rate calculated. Future Values is used to compute for the future value of an investment based on recurring periodic payments at an unvarying rate of interest

To use FV, here is an example:

• Suppose you save P200.00 a month for three years with an interest rate of 5%, you will have P7,750.67 at the end of the period

The PMT function allows you to compute the periodic payment amount for a loan on a car, house, or any other item.

To use PMT, here is an example:

• Suppose you loan money to a bank amounting to P100,000.00. And you have to pay this amount in 20 years with an interest rate of 3%. The monthly payment will be P554.60.

  The PMT requires you to enter the interest rate, length of loan (number of periods), and PV (Present Value, the amount you are borrowing or lending).

Use Logical Functions (IF)

Logical function is a powerful worksheet function that enables you to add decision-making and logical preference for your worksheets.

• IF() Function

• IF() statement is very useful for testing a cell and making decisions based on cell content.

• Syntax: IF(logical_test, value_if_true,value_if_false)

• IF statement requires three arguments:

• Logical_Test - is any value or expression that can be evaluated to TRUE or FALSE.

• Value_if_true - is the value that is returned if logical_test is TRUE.

• Value_if_false - is the value that is returned if Logical_test id FALSE.

To use the IF function using the keyboard, follow these steps:

1. Select the cell where you want the result to appear.

2. Type = (equal sign) and the function IF and an open parenthesis.

3. Type the correct arguments for the function, then type the close parenthesis.

1. Create a new worksheet. Input 10 students' name and their scores from Quiz 1 to 5 (each quiz is 20 items).

2. Compute for the total score of each student in column 6.

3. Create a formula in column H in order to determine if the students passes or failed. 50 and above = passed, 49 below = failed.

4. Use the illustration below as your guide.

Nested Ifs

You can have one nested function inside another one. In the first argument, you will test a condition. The second or third arguments, the value if true or false could have another If statement.

You can type nested Ifs in the Formula bar.

Whiz Words

Lesson Summary

=NOW() function allows you to put current day and time to your cell.

To calculate a date value, it requires three arguments such as year, month and day.

Financial functions generally deal with the time value of money and need the interest rate, number of payments loan or investment amount, and payment amount. FV (Future Value) computes for the value of an investment given periodic payments, an interest rate, and a term. PMT (payment) finds how much money you will have to pay if you borrow a certain amount at an interest rate and term.

IF() function allows you to look at a condition and place one result in the cell if the condition is true and a different answer if the condition is false.

Study Help

1. What is the difference between PMT and FV?

   ---

   ---

   ---

2. What are the three arguments needed in the IF function?

   ---

   ---

   ---

3. What is a logical function?

   ---

   ---

4. What are the three arguments needed in the Date function?

   ---

5. What is Nper?

   ---

   ---

1. Create a worksheet that would serve as a cash register.

2. Copy the illustration below and add formatting to it.

3. The cells under the CD Title, Price and Amount should have a nested IF formula so that once you input a code, it will automatically display the CD Title, Price and Amount.

4. Create a formula in E5 that computes for the Total Amount.

5. In Cell B27, the change should automatically be displayed once the amount of payment was entered on cell B26.

6. Save your work as CD Bar.xls at Excel-Activities folder.