Lesson 5 of 29, Logic & conditions
The IF function
IF asks a yes-or-no question and returns one of two answers.
Watch it done
2 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
=IF(test, value_if_true, value_if_false). The test compares something with =, <>, >, <, >= or <=.
Text answers go in double quotes; numbers don't. Answers can be calculations too: pay a bonus of B2*5% or 0.
Syntax
=IF(B2>=70,"Pass","Fail")Text answers=IF(C2="Yes",B2*0.1,0)Calculated answer=IF(B2>=$F$2,B2*$F$3,0)Rules in input cellsWatch out for
- Text in a test needs quotes: C2="Yes". Without them you get #NAME?.
- Check the boundary: does exactly 70 pass? That decides > versus >=.
The exercises
- Pass or fail. In C2, show Pass if the score is 70 or higher, otherwise Fail. Fill down to C7.
- Bonus calculator. In C2, pay a bonus of sales × the rate in F3 if sales meet the target in F2; otherwise 0. Fill down to C6.
Pass or fail
In
C2, show Pass if the score is 70 or higher, otherwise Fail. Fill down to C7.