Learning the "IF/Then" Calculation in Excel

Use IF statements in Excel spreadsheets to perform calculations.

Learning how to use the IF statement in Microsoft Excel will make working with the program more effortless and enjoyable. IF statements answer the question, "Is this true or is this false?" This means the IF statement tells Excel to perform an action only if something is true, and to perform another action (or do nothing) if something is false. "Then" is not an Excel command, but is implied and means "then do this" in the program. For instance, you can create a basic "IF/Then" statement to represent, "If the end of the year sales exceed $700K, then give everyone a 15 percent raise." In Excel, you can create IF statements to answer any logical question, no matter how complicated the question may appear.

Instructions

Building an IF Statement with One Condition

1. Click "File," then "New," "Blank Workbook" and "Create" on the Excel toolbar at the far left corner of the screen. This opens a new, blank workbook where you can create real or practice IF statements.

2. Enter the numbers 33 and 12 into cells A1 and A2. Enter 40 and 8 into cells B1 and B2.

3. Type the following IF statement into cell C1:

   =IF(A1>B1,"Yes","No")

   This statement means "If the number in A1 is greater than the number in B1, print 'Yes'. If A1 is not greater than B1, print 'No.' " Make certain to begin the statement in C1 with the equal sign and type the statement exactly as described. Since 33 is less than 40, you will see "No" in the C1 cell. Type the same statement into the C2 cell. Since the value of 12 is greater than the value of 8, you will see "Yes" in C2. You can use any words you choose for the result, or use sentences. Make certain to put quote marks around the words or sentence you want displayed as the result in your IF statement.

4. Repeat the IF statement automatically instead of retyping it into each cell. Click on the C1 cell, then grab the fill handle in the lower right cell corner with your mouse. You will see the fill handle turn into a black cross. Using your mouse, drag the cell down the column to cell C6. Turn your attention to the contents of cell C2 and notice how C2 automatically calculated the IF statement. When you dragged the cell down to C6, the IF statement was automatically copied from C1 and applied to cells C2 through C6. Enter random numbers into the A3 through A6 cells and the B3 through B6 cells. You will see the "Yes/No" results of the IF statement in the C column through cell C6.

5. Enter the IF statement into cell C1, typing over what is already there:

   =IF(A1>B1,100,1000)

   This statement means "If the number in A1 is greater than the number in B1, print '100;' otherwise, print '1000'." After you type the statement into cell C1, you will see "100" as a result, since 12 is greater than 8. Notice you do not need quotes in your IF statement for numeric values.

Building an IF Statement with More than One Condition

1. Enter M, F, F, M, F in cells A1 through A5. Do not enter quotation marks with these letters, which represent male and female.

2. Enter the numbers 12, 11, 5, 14 and 17 for cells B1 through B5. These numbers represent ages.

3. Type this IF statement into the C1 cell:

   =IF((AND(A1="M", B1>12)),"Give 2 tsp of medication.", "Give 1 tsp of medication.")

   This IF statement has two conditions and translates to, "If the child is male and over the age of 12, print 'Give 2 tsp of medication.'; otherwise print 'Give 1 tsp of medication.' " You will notice the C1 result reads, "Give 1 tsp of medication." Drag the box down to C5 to see the other results.

Tips & Warnings

• Practice creating IF statements for a variety of hypothetical conditions or for actual situations that can be used now. Once you understand how IF statements work, you can progress to more complex IF statements.

• Download a free Microsoft Excel trial version online if your computer does not have the program. Note that if an existing version of Excel is on the computer's hard drive, downloading a trial version may write over the existing Excel program.

References

• "Microsoft Office Specialist: Excel 2003 Study Guide"; Linda F. Johnson; 2006
• Linda's Computer Stop: Introduction to IF Statements in Excel

Resources

• Cnet Download.com: Microsoft Office Standard 2007