How do I Write the If Function in Microsoft Excel?

Save

In addition to performing basic arithmetic, Microsoft Excel can slice and dice data in sophisticated ways. One powerful set of functions rests on the simple "IF." Essentially, IF examines a criterion and determines whether it's true for some data. In combination with mathematical functions, IF can, for example, provide a count of cells that conform to a condition, or average a range of cells if a different range of cells conforms to that condition.

IF

  • Use IF to determine whether a condition is true or false. For example, use a formula in column B to evaluate a condition in column A, where the data in column A are a list of foods and you want to determine how many pineapples are in the list.

  • Type in cell B1, without the quotation marks, "IF(A1="pineapple",1,0)". Three pieces of information are inside the parentheses, separated by commas. The first is the criterion: Does the data in A1 match what's inside the quotation marks? The second tells Excel what to place in the cell if the condition is true: If A1 matches what's inside the quotation marks, Excel should place a 1 in cell B1. The third tells Excel what to place in the cell if the condition is false: 0 if A1 contains anything but precisely what's inside the quotation marks -- which is why that comma is outside the quotation marks in the formula.

  • Use text instead of values for true or false results for quick visual confirmation rather than further calculation. For example, type "IF(A1="pineapple","Yes", ""), and the result will show a "Yes" in B1 if A1 reads "pineapple" or appear blank if A1 contains any other value. Values do not require quotation marks, but text does. If you omit the last piece of information, after the final comma, and the result is false (A1 does not read "pineapple"), Excel enters "FALSE" in the cell.

  • Adjust the criterion based on what you need to determine, such as checking whether cell A1 exceeds 15 in value: =IF(A1>15, "More than 15", "15 or under") or if cells A1 and B1 are equal: =IF(A1=B1, "Same", "Different").

  • Copy and paste the formula to each cell in column B for which you want to evaluate the adjacent cell in column A.

COUNTIF, SUMIF, AVERAGEIF

  • Determine how many cells meet a condition using COUNTIF. Using the fruit example, have Excel count the number of cells in A1 through A20 that read "pineapple": =COUNTIF(A1:A20,"pineapple"). The parentheses contain two components: the cell range before the comma and the criterion after it.

  • Sum one range of cells if another range meets a condition. The components of this formula are the range to check for the condition, the condition and the range to add together if the condition is met. To sum the cells in B1 through B20 if their corresponding cells in A1 read "pineapple," the formula reads "=SUMIF(A1:A20,"pineapple", B1:B20)".

  • Average a range of cells if another range meets a condition. This formula is precisely the same as the SUMIF formula, only replacing "SUMIF" with "AVERAGEIF." In this case, it would read "=AVERAGEIF(A1:A20,"pineapple",B1:B20)".

Nested IF

  • Create several IF statements in one formula by nesting them. For this example, consider a price list. Say you have a list of 20 items: Widget A costs $6.75, Widget B costs $10, Widget C costs $2.50 and all other widgets cost $5. Assume the list is in column A and the prices are in column B. You can autopopulate the price list by having IF check the item names.

  • Begin the formula in cell B1, but do not press "Enter" yet: "=IF(A1="Widget A",6.75". This partial formula sets up the "True" value for A1 reading "Widget A."

  • Continue the formula with the "False" value, which is another IF statement: "=IF(A1,"Widget A",6.75,IF(A1,"Widget B",10" and the next "False" value until you reach the default value, $5 for all other widgets: "=IF(A1="Widget A",6.75,IF(A1="Widget B",10,IF(A1="Widget C",2.5,5)))". This formula tells Excel: Check if A1 is "Widget A." If it is, report 6.75. If it isn't, then check if A1 is "Widget B." If it is, report 10. If it isn't, then check if A1 is "Widget C." If it is, then report 2.5. If it isn't, then report 5.

Promoted By Zergnet

Comments

You May Also Like

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!