Have you ever wanted to test whether values in two columns are equal row-wise in Google Sheets? Look no further than the Delta function! In this article, we’ll explore the purpose, syntax, and formula examples of this handy function in Google Sheets. So let’s dive right in!
The Purpose of the Delta Function
The Delta function in Google Sheets is a simple yet powerful tool. Its purpose is to compare two numeric values and return 1 if they match, and 0 if they don’t. This makes it perfect for checking equality between values in different columns.
Syntax of the Delta Function
The syntax of the Delta function is straightforward. It takes two arguments, number1 and number2, and compares them for equality. If you omit the second argument, the function will compare the first number with 0 for equality. And the best part? You can even use the Delta function to compare dates, timestamps, and time entries!
Formula Examples
Let’s take a look at a few examples of how to use the Delta function in Google Sheets.
Compare Two Numbers and Return 1 if Match, Else Return 0
=delta(100,100)
Result: 1
=delta(22,25)
Result: 0Compare Two Dates and Return 1 if Match, Else Return 0
=delta(date(2019,10,29),today())The above formula compares the date 29/10/2019 with today’s date and returns 1 if they match, and 0 if they don’t. It’s that simple!
Please note that the Delta function treats a blank cell as the same as a zero.
Using Delta in Arrays
Now let’s see how the Delta function can be used in arrays. Here’s a real-life example to illustrate its usefulness.
Let’s say you have quotations from two vendors for 10 different products. Using the SUM function with Delta, you can count the number of items where the vendors match their prices.
The formula for this is as follows:
=ArrayFormula(SUM(delta(B3:B12,C3:C12)))But wait, there’s more! You can also use different formulas to achieve the same result. Here are two alternative formulas:
=ArrayFormula(sum(-eq(B3:B12,C3:C12)))
=ArrayFormula(SUM(-(B3:B12=C3:C12)))Cool, right?
So go ahead and give the Delta function a try in your Google Sheets. It’s a game-changer when it comes to comparing values and finding matches in your data.
To explore more tips and tricks for Google Sheets, visit Crawlan.com. You’ll find a treasure trove of Google Sheets insights and tutorials to help you become a spreadsheet wizard.
Happy Sheet-ing!
