Computer Training

Microsoft Excel 2003

Using the If Statement

The IF Statement is used to conduct a logical test of any value or expression that can be evaluated as True or False. Using the Loan Calculator (PMT) created in the earlier spreadsheet the If Statement can be used to diplay whether or not to make a purchase.

For example
In this example the user has determined that $250 is the maximum loan amount for their budget. Their decision to purchase will be reflected by one or all of the following factors, (1) amount borrowed, (2) interest rate, (3) down payment, and (4) term of loan.

How to:

1. Open the spreadsheet that contains the Loan Calculator from Exercise 2.
2. In cell A16 type Purchase, then Merge & Center cell range A16:B16. Next, use the Formatting Toolbar to Left Align and Bold the cell's contents.
3. Select cell C16 and the click on on the Insert Function button located on the Edit Formula Toolbar.
4. When the Insert Function dialog box opens click on the IF Function.
5. In the Argument box Logical_test enter the cell reference C8=<250. Press Tab.
6. In the Value_if_true box type Deal and then press Tab.
7. In the Value_if_false box type No Deal and then press Tab. Change any of the variables to test the formula.
8. Do not close the spreadsheet because it is used in the next example.
   

Using Goal Seek

Goal Seek is a tool used for a what-if analysis, whereas the desired result is known but not the input into the formula to obtain the result.

For example
In the example above we used the IF Statement to display whether or not to make a purchase based on a monthly payment that equaled or was less than $250. Goal Seek allows the user to obtain their goal by changing the input of a selected cell.

How to:

1. Click on the menu Tools | Goal Seek to open the Goal Seek Analysis dialog box.
2. In the above example a monthly payment that equaled or was less than $250 was desired. The new goal is a monthly payment that equals $195.
3. Type in the cell reference or collapse the dialog box and select the cell that you want to change. I choose cell C8, the Monthly Payment.
4. Enter the goal value 195 (no $ or decimals) in the To value box.
5. In the By changing box reference the Down Payment cell C5, . Click on OK to see the result.
6. Click on the Cancel button to return to the orginal input. Repeat the prior steps except this time in the By changing box select cell C7 (Months - the term of the loan).

Search | Site Map | Ask Scranton | Choosing Scranton | My Scranton | Campus Contacts