EXCEL CONDITIONAL OPERATIONS

IF STATEMENTS If statements are functions which can provide different answers based upon a user created criteria.

=IF(Condition,True,False) The condition. B2>H5 for example.

What to do if the condition is True.

What to do if the condition is False.

Any section of the if( ) statement may Contain a formula.

SIMPLE IF STATEMENTS

Example 1:

Simple Formula in True Section

Find Regular & Overtime Hours Based on Total Hours

Regular Hours (C4): Overtime Hours (D4):

=IF(B4>40,40,B4) =IF(B4>40,B4-40,0)

Regular Hours - Uses a condition between two cells to display a number or the contents of a cell Overtime Hours – Uses a condition between two cells to display the solution of a formula or a number.

2

Example 2:

Condition Checks for Text & Returns Formula Answers

Use an IF( ) statement to determine the Discount Price. Any address in zone "A" gets 10,000 knocked off the List Price of the house. All other zones only get 2,000 knocked off of the asking price.

Discount Price (D2):

=IF(C2="A",B2-10000,B2-2000)

Example 3: Function in Condition Section and Text in True & False Section If the movie made a profit greater than the average profit, we wish to "Buy" it; otherwise, we wish to "Lease" it.

Buy or Lease (C2)

=IF(B2>AVERAGE($B$2:$B$10),"Buy","Rent")

Note that the range for "average( ) must be absolute.

3

EXERCISE 1 – DETERMINE MONTHLY HOUSE PAYMENTS Use PMT to determine your monthly house payments. Also figure in: Taxes, PMI (use an IF statement), and Homeowners Insurance. Taxes: Approximate Yearly Rate is 1.25% of the Purchase Price of the house. (Varies by City) Mortgage:

Use Excel's PMT( ) Function

PMT (Private Mortgage Insurance) Approximate Yearly Rate is 1.1% of the Loan Amount; however, PMI is dropped when financing falls below 80% of the Purchase Price. For example, if you purchase a house for 100K and put 20K down, PMI is dropped. Or, if you get a first and second (i.e. borrow 80k from one bank and 20K from another bank), PMI is dropped. (Loan Amount / Price of House) Must be less than .80 to drop PMI Homeowners Insurance: Approximate Yearly Rate is 0.52% of the Loan Amount. Yearly Interest Rate:

Varies day by day. As of this writing it was about 6.65% per year.

Loan Duration in Years: Typically 30 years but 15 years is also available.

Mortgage: PMI: Taxes: Insurance:

=PMT(B8/12,B9*12,B7) =IF(B7/B5DATE(1993,6,1),"Purchase","Lease"),"Lease")

7

Example 4: Nesting If Statements to Produce Multiple Range Sections We wish to assign a letter grade of A, B, C, D or F to each score using the standard of 100 through 90=A, 80 through 89.9 = B, etc.

The nested "If Statements" are placed in the false section of the previous "If Statements". The key to understanding this "If Statement" is knowing that "If Statements" are read from left to right and as soon as a condition it true, Excel stops reading the rest of the statement. For example, 74 is less than both 80 and 90 but because the condition that checks to see if it is less the 80 is to the left of the condition checking to see if it is less than 90 and the condition is true, Excel never checks to see if it is less than 90.

A = 90 - 100 B = 80 - 89.99 C = 70 - 79.99 D = 60 - 69.99 F < 60

=IF(B2,=, 40

(Note the use of TO and that smaller numbers must be listed first) (Note use of IS when using >,