Like everything else, Excel has a remedy that makes this test easier. There are many formulas you could write that check if two values in Excel are equal or not. Two of the simpler ones consist of the DELTA function and the IF function.
What Is the DELTA Function in Excel?
DELTA is a function in Excel that tests if two numerical values are equal or not. If the two values are equal, DELTA will return 1, and if they are not equal, it will return 0.
The DELTA function can only operate on numbers, and it cannot test whether two text strings are equal or not. If number2 is left blank, DELTA will assume that it is zero.
How to Test if Two Values Are Equal With the DELTA Function
DELTA can be easily coupled with other Excel functions to count the equal number pairs in a list. In this sample spreadsheet, we have two columns of numbers, and we want to see how many pairs are equal.
For this example, we’re going to use the DELTA function to check if the numbers in each pair are equal. Then, we’re going to get the count of equal pairs using Excel’s COUNTIF function. Here’s how:
Select the first cell in the column where you want to check if the numbers are equal. In this example, we’ll use cell C2. In the formula bar, enter the formula below: =DELTA(A2, B2) Press Enter. DELTA will now tell you if the two numbers are equal or not. Grab the fill handle and drop it on the cells below to get the test results for all the numbers.
The formula we used here calls on DELTA to test the numbers in A2 and B2 and see if they’re equal. The formula then returns 1 to indicate that they were equal. If the two numbers weren’t equal, the formula would return 0.
Now you can see which pairs have equal numbers using the DELTA function. Unfortunately, that’s as far as the DELTA function goes. To count the number of pairs with equal numbers, you’ll have to use other functions. One candidate for this task is the COUNTIF function.
COUNTIF can go through a range of cells and return the number of cells that meet a specific condition. Since DELTA returned 1 to indicate equal pairs, we can ask COUNTIF to go through DELTA’s output and count the cells containing 1. This will give us the number of equal pairs.
Select the cell where you want to show the count of pairs with equal numbers. In the formula bar, enter the formula below: =COUNTIF(C2:C10, “=1”) Press Enter.
The formula calls on COUNTIF to check the cells C2 to C10 (the results from the DELTA function) and return the number of cells that equal 1.
Remember that the DELTA function returns 1 when the two values are equal, so this formula will count the number of pairs with equal numbers. You can replace 1 with 0 in the formula to get the count of pairs with unequal numbers.
The DELTA function is a simple enough method to check if two numbers are equal. However, if you don’t like the binary output of the DELTA function, you can use the IF function instead to get a custom output.
What Is the IF Function in Excel?
IF is one of the core Excel functions that lets you build sophisticated formulas. It takes a condition and then returns two user-specified outputs if the condition is or is not met.
The IF function conducts a logical test, and if the test result is true, it returns Output_If_True. Otherwise, it returns Output_If_False.
IF is on a whole other league than DELTA. Where DELTA could only determine whether two numbers are equal, IF can run any kind of logical tests. Another advantage of IF to DELTA is that it isn’t restricted to numbers. In addition to numbers, you can check whether two strings of text are equal using IF in Excel.
How to Test if Two Values Are Equal With the IF Function
In this context, the IF function works in the same way as the DELTA function, except that you can have it output a specific string. To check if two values are equal with the IF function, you need to run a logical test where you put the two cells as equal.
Then, you’ll need to specify outputs for the two scenarios: The test result being true, and the test result being false.
Let’s put the IF function to use in the same example. We’re going to see which numbers pairs are equal, and then count the equal ones using the COUNTIF function.
Select the first cell in the column where you want to return the test results. This will be cell C2 for this example. In the formula bar, enter the formula below: =IF(A2=B2, “Yes”, “No”) Press Enter. The IF function will now tell you whether the two values are equal or not. Grab the fill handle and drop it on the cells below. The IF function will now test each pair and return the results accordingly.
The formula we used will test cells A2 and B2 to see if they’re equal. If the two cells are equal, the formula will return Yes. Otherwise, the formula will return No.
You can count the equal pairs using the COUNTIF function in the same way as the previous example, except that instead of counting the cells that equal 1, you should count the cells containing the string Yes.
This is because the IF formula you wrote returns Yes for pairs that contain equal values. Here’s how you can count the equal pairs:
Select the cell where you want to return the count. For this example, we’ve chosen cell E2. In the formula bar, enter the formula below: =COUNTIF(C2:C10, “=Yes”) Press Enter. COUNTIF will now tell you how many pairs contain equal values.
This formula will summon COUNTIF to look through cells C2 to C10 (the results from the IF function) for cells containing the string Yes and then return the count of cells that do.
Since IF returned Yes for equal pairs, then this will be the count of equal pairs.
Test if Two Values Are Equal With Excel Functions
There are plenty of formulas you could write up to check whether two values are equal in Excel. The DELTA function is designed to serve this purpose exclusively, whereas the IF function can accomplish many feats, this included.
Excel exists to take the burden of counting and calculating off your shoulders. This is one minor instance of how Excel can do that, and make your life easier as a result.
