What Is DELTA Function In Google Sheets?
The DELTA function in Google Sheets is an inbuilt Engineering function that accepts two numbers to compare them. Its output is the value 1 when the two specified numbers are equal. Otherwise, the function returns 0 as the output.
Users can use the function DELTA in Google Sheets for conditional formatting, dynamic data range comparison, error checking, and data validation.
For example, the dataset below shows two sets of values in columns A and B.
We must assess whether the two values in each row are the same and show the output in column C.
Then, adhering to the context of the DELTA function in Google Sheets explained earlier, applying the DELTA function in column C cells will fetch us the targeted outcome.
The above example shows that the DELTA function in Google Sheets arguments count is two in a target cell, which are the numbers from the two value sets in the specific row.
The function then compares the two input values to evaluate if they are equal. In the case when the two numbers are equal, the DELTA function in Google Sheets returns 1. Otherwise, the function returns 0.
Table of contents
Key Takeaways
- The DELTA function in Google Sheets accepts two numeric values and compares them to confirm if they are equal or not. If the two numbers are equal, the function return value is the value 1. Otherwise, it returns 0.
- The function DELTA in Google Sheets finds applications in data validation, conditional formatting, error checking and handling, and dynamic data range comparisons.
- While we can use the DELTA function individually in Google Sheets, applying it with other inbuilt functions, such as IF and ARRAYFORMULA, helps achieve practical results.
DELTA() Google Sheets Formula
The syntax for the DELTA() in Google Sheets is as follows:
Where,
- number1: The first numeric value to compare.
- number2: The second numeric value to compare.
We must supply the first argument when using DELTA function in Google Sheets, as it is compulsory. However, the second argument is optional, and when we ignore it, the function takes its default value of 0 as the input.
Furthermore, if any of the argument values are non-numeric, the DELTA function in Google Sheets returns the #VALUE! error value.
How To Use DELTA Function In Google Sheets?
We can apply the function DELTA in Google Sheets using the below methods:
- Access the function from the ribbon.
- Enter the function into the sheet manually.
Method #1 – Access The Function From The Ribbon
Choose a specific cell to showcase the output – The Insert tab – The Function option right arrow – The Engineering function group right arrow – The DELTA function.
The specified function is shown in the selected cell. Next, update the DELTA function in Google Sheets arguments, separated by commas, within the brackets.
We may click the ‘?’ icon next to the chosen cell to check the syntax of the DELTA function in Google Sheets explained previously.
Next, clicking the down arrow at the top right corner of the syntax box will show the function’s detailed explanation.
Finally, after updating the arguments, press Enter to view the DELTA function return value.
Method #2 – Enter The Function Into The Sheet Manually
- Select a specific cell to showcase the desired outcome.
- Type =DELTA( in the cell. [ Alternatively, type =D or =DE and click the DELTA function from the listed options to insert the specific function in the selected cell.]
- Update the applicable argument values with commas in between, and close the brackets.
- Press Enter to acquire the DELTA() output.
Examples
The examples below explain the different strategies for using DELTA function in Google Sheets.
Example #1
The given dataset shows the monthly revenue data at two branch offices of a firm.
The task is to compare the revenues of the two branch offices to verify if they are the same and showcase the output in column D. Furthermore, the output should be in 1s and 0s.
Step 1: Click cell D2 and enter the DELTA().
=DELTA(
[Alternatively, click on the specific cell. Next, choose Insert – Function – Engineering – DELTA function.
We can view the specific function in the target cell, as depicted above.]
Step 2: Type in the function arguments inside the brackets.
=DELTA(B2,C2)
Step 3: Press Enter to fetch the DELTA function return value.
Next, choose cell D2, and using the fill handle, copy the function in the rest of the target cells.
The DELTA() accepts two arguments. While the first one is the reference to the cell holding the first branch office’s revenue value, the second is the reference to the cell holding the second branch office’s revenue value.
Next, the function compares the two numeric values. It then returns 1 if the two values are the same. Otherwise, the function returns 0.
Example #2
We have two datasets showing the list of the same employees and their dates of joining.
We must check the two datasets to confirm if the date of joining of each employee is the same in both datasets. Assume column H cells are the target cells.
Then, we may use the DELTA function with the IF function in the specified cells to acquire the required result.
Step 1: Choose the specific cell H3, enter the following formula, and press Enter.
=IF(DELTA(B3,E3),”Dates Match”,”Dates Mismatch Found”)
Step 2: Click cell H3. Next, using the fill handle, which works like the Excel fill handle, copy the formula in the remaining specified cells.
Let us check the cell H7 formula to understand the logic.
Firstly, the DELTA(), which is the IF() condition, accepts the references to the cells B7 and E7 values, 14-July-2024 and 13-July-2024. It compares the two dates for a match. Since the two dates are not the same, the DELTA() returns 0.
Next, the value 0 that DELTA() returns represents the Boolean value of False. Thus, the IF() condition is false, leading to the IF(), which works like the Excel IF function, returning the false value, Dates Mismatch Found as the output.
Example #3
The dataset below shows product codes and their required and actual inventory levels.
We must compare the required and actual inventory levels for each product code. We must display the comparison results by highlighting and mentioning the phrase “Same” in the corresponding column D cells in the rows where the two values are equal.
Step 1: Select the range D2:D11 and choose Format – The option Conditional formatting in Google Sheets, similar to the Conditional Formatting in Excel.
The Conditional format rules pane opens. It shows the chosen range in the first field, Apply to range.
Step 2: Set the second field, Format rules, as the Custom formula is option using the field’s dropdown list.
Next, update the applicable custom formula:
=DELTA(B2,C2)
Click Done to view the newly-created rule in the pane. It indicates that the DELTA() in each column D cell compares the corresponding columns B and C cell values to assess if they are equal.
So, the specific column D cell should get highlighted in the chosen color if the DELTA() output is 1.
Next, close the pane.
A few column D cells appear highlighted. The highlighted cells are where the custom formula output is True (or 1).
Step 3: Select the first highlighted cell D3, enter the phrase “Same”, and press Enter.
Next, enter the same phrase in the remaining highlighted cells, as depicted below.
Important Things To Note
- Assume that one of the argument values supplied to the DELTA function in Google Sheets is non-numeric. Then, the function’s return value will be the #VALUE! error value, similar to the #VALUE! error in Excel.
- Assume that one of the argument values provided to the Google Sheets DELTA function is an error value. Then, the function’s return value will be the same error value specified as the function’s argument value.
Frequently Asked Questions (FAQs)
We can use the Google Sheets DELTA function in an array as described below with the help of an example.
The following dataset holds a set of fruits and their units sold data for May and June months.
We need to compute the total count of fruits with an equal number of units sold figures in May and June. Let us take cell E2 as the target cell.
We can achieve the required outcome using the DELTA function with the ARRAYFORMULA function in the specified cell.
Step 1: Choose the specific cell E2, enter the following formula, and press Enter.
=ARRAYFORMULA(SUM(DELTA(B3:B11,C3:C11)))
The SUM() nesting the DELTA() executes as an array formula.
First, the DELTA() returns an array of 1s and 0s. The array value will be 1 when the corresponding columns B and C cell values are equal. Otherwise, the array value will be 0. So, the resulting array is {0;1;1;0;1;0;0;1;1;0}
Finally, the SUM() adds the array values of 1s and 0s to return the value 5 as the required result.
The Google Sheets DELTA function is not working because one or both argument values are non-numerical. Otherwise, one of the argument values is an error value.
We can use the DELTA function with Data Validation in Google Sheets using the below steps:
1. List the first and second sets of numeric values in two columns.
2. Select the second data range and choose Data – Data validation.
3. The Data validation rules pane appears on the right of the workspace. The first field, Apply to range, shows the chosen range. We must update the second field, Criteria, as the Custom formula is option. Next, enter the DELTA() in the next field, with the references to the first cells of the two source data ranges being the arguments.
4. Click Done and close the Data validation rules pane.
The given dataset will show the second data range with the Red triangle at the top right corner of the cells where the DELTA() output is 0. The Red triangle represents an error message indicating that the two values in the corresponding row of the two data ranges are not equal.
Download Template
This article must be helpful to understand the DELTA Function In Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is DELTA Function In Google Sheets. We learn how to use it to compare two numbers with step-wise examples. You can learn more from the following articles –
Graphs Vs Charts In Google Sheets
Leave a Reply