Making decisions with IF
7 minutes · 3 steps · free, no sign-up
IF lets a spreadsheet choose between two answers. You will apply a shipping rule to a list of orders: free over a threshold, otherwise paid. Then you will copy the formula down so every order is handled by the same rule.
- Write a comparison like B2>=50
- Use IF to return one of two answers
- Fill a formula down a column
- See results change when an input changes
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceThe IF function
=IF(test, value_if_true, value_if_false)
=IF(B2>=50, "Free", "Paid")
Comparisons: = <> < > <= >=The key ideasSummary: Making decisions with IF
IF takes three parts: a test, the answer if the test is true, and the answer if it is false. Text answers go inside double quotes. The test is usually a comparison such as B2>=50.
Pay attention to the boundary. Order 103 is exactly 50, and >= counts it as free while > would not. Boundary values are where most IF bugs hide, so test them on purpose.
When you fill a formula down, Google Sheets adjusts the references for each new row, so B2 becomes B3, then B4. That is called a relative reference, and it is why one formula can handle a whole column.
Watch out forCommon mistakes
- Forgetting the quote marks around text.
Freewithout quotes is read as a name Sheets does not know, so you get an error. Text answers always go in"double quotes". - Getting the boundary wrong.
>50and>=50differ for a total of exactly 50. Decide which you mean, then test a value on the line. - Swapping the two answers. The second part is for true and the third for false. If every result looks reversed, they are probably the wrong way round.
Next stepTry it in the real Google Sheets
When you type =IF( in Google Sheets, a small tooltip lists the arguments and highlights the one you are on, which helps when formulas get long. To copy a formula down a column you can use Ctrl+D as here, or drag the small blue square at the bottom-right corner of the cell. Double-clicking that square fills down as far as the data next to it goes.