DELTA function

PRO BASIC

The DELTA function in Jspreadsheet Formulas Pro is a simple tool used to compare two values. If the values you're comparing are exactly the same, the function will return a 1. However, if the values are different in any way, the function will instead return a 0. This can be particularly useful when you're analyzing data and want to quickly identify matches or discrepancies.

Documentation

Tests whether two values are equal. Returns 1 if the values are equal, or 0 otherwise.

Syntax

DELTA(number1, [numberN])

Parameter	Description
`number1`	The first value you want to compare.
`numberN`	Optional. The second value you want to compare. If omitted, assumes a default value of zero.

Behavior

The DELTA function compares two numeric values, returning 1 if they are equal and 0 if they are different. Here is how it behaves under different circumstances:

Empty Cells: If one or both of the arguments of the DELTA function are empty cells, the function will treat them as 0.
Text: If one or both of the arguments are text, the function will return #VALUE! error.
Booleans: If one or both of the arguments are boolean values, the DELTA function will convert TRUE to 1 and FALSE to 0.
Errors: If one or both of the arguments are cells that contain errors, the DELTA function will return the same error.

Common Errors

Error	Description
#VALUE!	This error occurs when one or both of the arguments are non-numeric values.
#REF!	This error occurs when the cell reference provided as an argument is not valid.
#NAME?	This error occurs when the function name is misspelled or the function does not exist.

Best practices

Ensure that the arguments are numeric. If there's a chance that the cells may contain non-numeric values, use error checking functions to prevent #VALUE! errors.

Use absolute cell references if you want to compare a specific cell with a range of cells. This prevents the reference cell from changing when the formula is copied across the range.

Avoid using nested DELTA functions as it can make the formula complex and difficult to understand. Use other logical functions like IF or AND to add more conditions.

Since the DELTA function can only compare equality, it's not suitable for range comparisons. Use other functions like COUNTIFS for such scenarios.

Usage

A few examples using the DELTA function.

DELTA(5, 5)          // Returns: 1 – values are equal
DELTA(10, 20)        // Returns: 0 – values are different
DELTA(3)             // Returns: 0 – compares 3 to default value 0
DELTA(TRUE, 1)       // Returns: 1 – TRUE coerces to 1
DELTA(FALSE, 1)      // Returns: 0 – FALSE coerces to 0
DELTA("5", 5)        // Returns: #VALUE! – text string not accepted

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTNmZjRjZjgzMzk5MWYyZmFjMDNhYzI2ZmNmNGIxN2Q4MTcxNjQwNGY1YWU3ZjUzOGQ4NjFlMzkwMjljNjk1NGYzOGEzNGZiZDQyMTJlZWNmOTM0NzYzNTk4OGQ5NWMwZmY3MzZjZTM0OTVlYTAzNzA5ZmI2Y2UzYWE3ZTQ1YzksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNRFF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Expected",
        "Actual",
        "Match"
    ],
    [
        100,
        100,
        "=DELTA(A2,B2)"
    ],
    [
        250,
        245,
        "=DELTA(A3,B3)"
    ],
    [
        75,
        75,
        "=DELTA(A4,B4)"
    ],
    [
        180,
        185,
        "=DELTA(A5,B5)"
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('OTNmZjRjZjgzMzk5MWYyZmFjMDNhYzI2ZmNmNGIxN2Q4MTcxNjQwNGY1YWU3ZjUzOGQ4NjFlMzkwMjljNjk1NGYzOGEzNGZiZDQyMTJlZWNmOTM0NzYzNTk4OGQ5NWMwZmY3MzZjZTM0OTVlYTAzNzA5ZmI2Y2UzYWE3ZTQ1YzksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNRFF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Expected",
        "Actual",
        "Match"
    ],
    [
        100,
        100,
        "=DELTA(A2,B2)"
    ],
    [
        250,
        245,
        "=DELTA(A3,B3)"
    ],
    [
        75,
        75,
        "=DELTA(A4,B4)"
    ],
    [
        180,
        185,
        "=DELTA(A5,B5)"
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('OTNmZjRjZjgzMzk5MWYyZmFjMDNhYzI2ZmNmNGIxN2Q4MTcxNjQwNGY1YWU3ZjUzOGQ4NjFlMzkwMjljNjk1NGYzOGEzNGZiZDQyMTJlZWNmOTM0NzYzNTk4OGQ5NWMwZmY3MzZjZTM0OTVlYTAzNzA5ZmI2Y2UzYWE3ZTQ1YzksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNRFF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Expected",
        "Actual",
        "Match"
    ],
    [
        100,
        100,
        "=DELTA(A2,B2)"
    ],
    [
        250,
        245,
        "=DELTA(A3,B3)"
    ],
    [
        75,
        75,
        "=DELTA(A4,B4)"
    ],
    [
        180,
        185,
        "=DELTA(A5,B5)"
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('OTNmZjRjZjgzMzk5MWYyZmFjMDNhYzI2ZmNmNGIxN2Q4MTcxNjQwNGY1YWU3ZjUzOGQ4NjFlMzkwMjljNjk1NGYzOGEzNGZiZDQyMTJlZWNmOTM0NzYzNTk4OGQ5NWMwZmY3MzZjZTM0OTVlYTAzNzA5ZmI2Y2UzYWE3ZTQ1YzksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNRFF5TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "Expected",
        "Actual",
        "Match"
    ],
    [
        100,
        100,
        "=DELTA(A2,B2)"
    ],
    [
        250,
        245,
        "=DELTA(A3,B3)"
    ],
    [
        75,
        75,
        "=DELTA(A4,B4)"
    ],
    [
        180,
        185,
        "=DELTA(A5,B5)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub