edubun
English
English한국어EspañolPortuguêsFrançaisDeutschItalianoРусский
Home›Spreadsheets and data›Spreadsheet formulas

Making decisions with IF

=IF(B2>=60, "Pass", "Fail") reads like a sentence: if the score is at least 60, write Pass, otherwise write Fail.

Try it first: does exactly 60 pass?

Before any rules, open the lab below. Ana scored 72, Ben 45, Chloe exactly 60 and Dev 59.5. Type =IF(B2>=60,"Pass","Fail") in C2 and fill it down. Then change >= to > in C4 and see what happens to Chloe. The formula reads almost like a sentence: if the score is at least 60, write Pass, otherwise write Fail.

Loading

Drag to turn. Scroll or pinch to zoom.

Chloe has exactly 60 and Dev has 59.5, so the choice between > and >= decides their results. Write C2, then fill down.

What you just typed: a test and two answers

IF takes three arguments: a logical test, the value to return when the test is TRUE, and the value to return when it is FALSE. Microsoft writes it as IF(logical_test, value_if_true, [value_if_false]) and calls it one of the most popular functions in Excel. Sheets uses the same shape. The test is usually a comparison like B2>=60, a tiny formula of its own that gives TRUE or FALSE, and IF picks one of its two answers from that. For Chloe's 60, B4>=60 is TRUE and B4>60 is FALSE. That single difference is what you just saw.

ComparisonMeaningWith B2 = 72
B2=72equal toTRUE
B2<>72not equal toFALSE
B2>60greater thanTRUE
B2<60less thanFALSE
B2>=72at leastTRUE
B2<=71at mostFALSE

The answers can be text, numbers or calculations. Text goes in double quotes, with one exception Microsoft points out: TRUE and FALSE, which Excel understands on their own. So =IF(C2="Yes",1,2) returns a number, and =IF(C2>B2,C2-B2,0) returns how far over budget a figure is, or 0 when it isn't over. Two quotes with nothing between them, "", return empty-looking text. That is the usual way to leave a cell blank when there is nothing to report.

Text comparisons ignore upper and lower case, so "yes"="YES" is TRUE. An empty cell compared with "" is also TRUE, which makes =IF(A2="", "missing", A2*2) a handy guard against blanks. Google's help names the most common mistake with IF: putting the two answers in the wrong order. Read the formula aloud as "if this, then that, otherwise the other", and a swap usually jumps out.

IF works well across a column. Write it once for the first row, with a relative reference to that row's score, and fill down; each student gets a result. Because every row has its own copy, you can sort the list or add students below and fill again. If the test compares with a fixed limit kept in a cell, such as a pass mark, that reference needs dollar signs, exactly like the tax rate in the first unit of this course.

Leaving out the third argument

Both programs mark value_if_false as optional. Leave it out, let the test be false, and Excel returns the logical value FALSE, which looks odd in a column of words. Microsoft's list of common IF problems also mentions a 0 in the cell when an answer argument is missing. Write both answers every time, even when one is just "". There is a quieter benefit too. Only the chosen branch is calculated, so =IF(B2=0, "none", A2/B2) never shows #DIV/0!; the division runs only when B2 is not 0.

An IF doesn't have to end in words. =B2*IF(C2="member",0.9,1) charges members 90% of the price and everyone else the full price. Here IF returns a number and the multiplication uses it like any other value, so one formula per row replaces two separate columns.

Write the limit in a cell and point at it with an absolute reference, such as =IF(B2>=$F$1, "Pass", "Fail"). When the pass mark changes, you change one cell instead of every formula.
Common mistakes
  • Using > when the rule says "60 or more". =IF(B4>60,...) fails a score of exactly 60. "At least" is >=.
  • Writing =IF(A1>5,Yes,No) without quotes. Yes and No are text and need quotes; without them the formula shows #NAME?. TRUE and FALSE are the exception.
  • Swapping the two answers. =IF(B2>=60,"Fail","Pass") runs without any error and labels every result backwards. Google's help calls the order the most common IF mistake.
  • Leaving out value_if_false. When the test is false, Excel shows FALSE in the cell. Write "" if you want it to look empty.
Teacher's noteThe syntax is rarely the problem. The boundary is. Learners who can recite IF(test, if true, if false) still write > for "60 or more", so test with a score of exactly 60 every time. A good order for a lesson: comparisons on their own first (type =B2>=60 and read the TRUE or FALSE), then wrap them in IF. Seeing a bare TRUE makes it clear what IF is choosing between. Ask learners to read each formula aloud as a sentence before filling it down.
  1. B2 holds 72. What does =IF(B2>=60, "Pass", "Fail") return?
  2. B5 holds 59.5. What does =IF(B5>=60, "Pass", "Fail") return?
  3. B4 holds 60. What does =IF(B4>60, "Pass", "Fail") return?
  4. B2 holds 72. What does =IF(B2>70,B2-70,0) return?
  5. B2 holds 0. What does =IF(B2=0, "none", A2/B2) return?
    • none
    • #DIV/0!
    • 0
    • FALSE
  6. In Excel, what does =IF(5>9,"big") return?
    • FALSE
    • big
    • #N/A
    • #VALUE!
  7. Which formula needs quote marks added to work?
    • =IF(A1>5,Yes,No)
    • =IF(A1>5,1,0)
    • =IF(A1>5,TRUE,FALSE)
    • =IF(A1>5,A1,0)
  8. What does ="yes"="YES" return?
    • TRUE
    • FALSE
    • #VALUE!
    • yesYES
  9. A2 is empty. What does =IF(A2="", "missing", A2*2) return?
  10. What is the most common mistake with IF, according to Google's help?
    • Putting the true and false answers in the wrong order
    • Using numbers as answers
    • Using >= instead of >
    • Writing IF in lower case
Key points
  • IF(test, value if TRUE, value if FALSE).
  • Comparisons: = <> > < >= <=. Text comparisons ignore case.
  • Text answers go in quotes; TRUE and FALSE do not need them.
  • Always give both answers; only the chosen one is calculated.
Sources for this course (19)
  • Microsoft Support, "Switch between relative, absolute, and mixed references" (support.microsoft.com/en-us/office/dfec08cd-ae65-4f56-839e-5f0d8d0baca9: $ before column and row, F4 to switch, copying two down and two right: $A$1 stays, A$1 to C$1, $A1 to $A3, A1 to C3)
  • Microsoft Support, "AVERAGE function" (support.microsoft.com/en-us/office/047bac88-d466-426c-a32b-8f33eb960cf6: text, logical values and empty cells in a reference ignored, zeros included; 10, 7, 9, 27, 2 gives 11, with 5 gives 10; mean 5, median 4, mode 3 for 2 3 3 5 7 10)
  • Microsoft Support, "COUNT function" (support.microsoft.com/en-us/office/a59cd7fc-b623-4d93-87a4-d23bf411294c: only numbers counted, dates count, errors and text do not)
  • Microsoft Support, "ROUND function" (support.microsoft.com/en-us/office/c018c5d8-40fb-4053-90b1-b3e7f61a213c: num_digits required; 2.15 to 2.2, -1.475 to -1.48, 626.3 to 1000, 1.98 to 0, -50.55 to -100)
  • Microsoft Support, "IF function" (support.microsoft.com/en-us/office/69aed7c9-4e8a-4755-a9bc-aa8bbff73be2: value_if_false optional, text in quotes except TRUE and FALSE, 0 and #NAME? problems)
  • Microsoft Support, "VLOOKUP function" (support.microsoft.com/en-us/office/0bbc8083-26fe-4963-8ab8-93a18ad188a1: range_lookup TRUE by default, sorted first column, #N/A, #REF!, #VALUE!, absolute table range, wildcards with FALSE)
  • Microsoft Support, "XLOOKUP function" (support.microsoft.com/en-us/office/b7fd680e-6d10-43e6-84f9-88eae8bf5929: not in Excel 2016 and Excel 2019, exact match by default)
  • Microsoft Support, "How to correct a #VALUE! error" (support.microsoft.com/en-us/office/15e1b616-fbf2-4147-9c0b-0a11a20e409e: + and * may not calculate cells with text, use SUM instead, IFERROR hides all errors)
  • Microsoft Support, "Excel specifications and limits" (15 digits, 64 nested levels, 255 arguments)
  • Google Docs Editors Help, "VLOOKUP" (support.google.com/docs/answer/3093318: is_sorted TRUE by default, FALSE recommended, search down to a larger value, " Apple" and "Apple " differ, wildcards, IFNA, absolute range)
  • Google Docs Editors Help, "XLOOKUP" (support.google.com/docs/answer/12405947)
  • Google Docs Editors Help, "ROUND" (support.google.com/docs/answer/3093440: places optional, 0 by default, rounding by magnitude)
  • Google Docs Editors Help, "IF" (support.google.com/docs/answer/3093364: order of the answers is the most common problem, IFS)
  • Google Docs Editors Help, "AVERAGE" (support.google.com/docs/answer/3093615: text ignored, any number of arguments)
  • Google Docs Editors Help, "COUNTA" (support.google.com/docs/answer/3093991: zero-length strings and spaces count)
  • Google Docs Editors Help, "Keyboard shortcuts for Google Sheets" (support.google.com/docs/answer/181110: F4 and Fn+F4 while entering a formula, F4 as Redo)
  • Pryor Learning and Exceljet (F4 cycle A1, $A$1, A$1, $A1; = comparison not case-sensitive)
  • Ablebits (IF without value_if_false returns FALSE)
  • Spreadsheet Class (Sheets circular dependency shows #REF!)

Checked: 2026-10-19