Logic Testing in MS Excel

Logic testing is one of the most commonly used functions in MS Excel. When we say logic testing, we usually refer to the IF function. To apply the IF function, we first need to give the logic test, which is the condition we want to test using this function. Next, we need to define the response if this condition is true, and finally, we need to define the response if this condition is false.

When we apply this function, it looks as follows:

 Let us look at some examples of how to use this function.

Example 1

Let us assume that we have the following data:

We need to see if the values in column B are equal to those in column A. Also, we can use logic tests for this purpose. We will apply the IF function and give the condition that if the value in column A is equal to the value in the same row of column B, then this condition is true.

If this condition is true, then we want the cell to say ‘YES’. We will write ‘YES’ in inverted commas, as this is the text that we wish this cell to show in case the condition is met. We want our cell to say ‘NO’ if this condition is false. Again, we write ‘NO’ in inverted commas and close the function. We paste this formula for the other rows, and the following is the result:

Similarly, we can use this function to test other such conditions:

Example 2

Let us now use a little more complex scenario. Suppose we have the data for students that have appeared in an exam. The students and their scores are as follows:

The passing score for the students is 50%, provided in cell B10. We can use the IF function to see whether the students have passed. For this, we first write the condition that if a student's score is equal to or greater than the passing score of 50%, then this condition is true, and we want the cell to show ‘pass’. Otherwise, we want the cell to show that the student has failed.  

When writing this function, it is important to lock the cell reference for the passing score, which here is cell B10. We can lock the cell reference by putting the $ sign before the column and row reference. We then paste this formula for the rest of the students, and the spreadsheet shows us whether the students have passed or not, as seen in the following figure:

As seen here, the logic test can have numerous practical applications. Therefore, it is a handy function, when working with spreadsheets.

Further Reading

• ACCA Qualification
• CIMA Qualification
• CPD for Finance Professionals

Study with Learnsignal: ACCA, CIMA, and CPD courses for finance professionals. Compare courses.