IF function and logical tests

Today, we will dis­cuss log­i­cal tests and the IF func­tion in Excel. 

Let’s start with log­i­cal test. We want to check whether our stu­dents passed the test, i.e. whether they achieved 70 points (cell D2). We need a for­mu­la that checks if the num­ber from cell B5 is greater or equal to the val­ue from in cell D2. We aslo have to lock the val­ue from cell D2 by press­ing F4 key (Fig. 1). 

After copy­ing the for­mu­la down, we have our log­i­cal answers (Fig. 2)

Log­i­cal answers, how­ev­er, aren’t human answers. If we want some­thing sim­pler, like pass/fail, we need to use the IF func­tion in cell D5 (Fig. 3). 

After copy­ing it down, we have the answers that we want, instead of log­i­cal ones. If the log­i­cal test returns False, our IF func­tion returns text Fail. If the log­i­cal test returns True, our IF func­tion returns text Pass (Fig. 4)

Some­times, we want to know how many more points a stu­dent needs to pass their exam. In that case, we also use the IF func­tion. This time, how­ev­er, we are chang­ing the direc­tion of our test. We need to check if the val­ue from cell B5 is low­er than the val­ue from cell D2 (70 points). If it is low­er, it means that the stu­dent failed the exam. Cal­cu­lat­ing the num­ber of miss­ing points is a sim­ple, math­e­mat­i­cal oper­a­tion. We just have to sub­tract the val­ue from cell B5 (62 points) from the val­ue in cell D2 (70 points). How­ev­er, if a stu­dent passed the exam, we don’t need to cal­cu­late any­thing, so we’re return­ing 0 points (Fig. 5). 

=IF(B5<$D$2,$D$2‑B5,0)

After copy­ing it down, we can see that one stu­dent needs 8 points to pass, and anoth­er one needs 35. The stu­dents who passed the exam, don’t need any more points, that’s why IF func­tion returned 0 for them (Fig. 6). 

https://www.youtube.com/watch?v=baC8XZ5G4D8