Blog Home / Data Analysis / Logic Testing in MS Excel

Logic Testing in MS Excel

One of the most often utilised features in Logic Testing in MS Excel. The IF function is typically mentioned when we talk about logic testing.

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:

Logic Testing in MS Excel

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

Example 1

Let us assume that we have the following data:

2 - Logic Testing in MS Excel

We need to see if the values in column B are equal to those in column A. We can use logic test 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 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 following is the result obtained:

3 - Logic Testing in MS Excel

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

4 - Logic Testing in MS Excel

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 shown as follows:

5 - Logic Testing in MS Excel

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:

6 - Logic Testing in MS Excel

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

Ozair Siddiqui
3 min read
Related:
Data AnalysisCPD
Locking Cell References in MS Excel
Ozair Siddiqui 20 January 2023
Data AnalysisCPD
Logic Testing in MS Excel
Ozair Siddiqui 20 January 2023

Shares

Leave a comment

Your email address will not be published. Required fields are marked *