XOR function
PRO
BASIC
The XOR
function in Jspreadsheet Formulas Pro is used to carry out a logical exclusive OR operation on all its arguments. It checks two or more conditions and returns TRUE if an odd number of conditions are TRUE, and FALSE otherwise. This means if both conditions are true or both conditions are false, it will return FALSE. This function is especially useful in situations where you need to verify that only one specific condition is met in your data set.
Documentation
Returns a logical exclusive OR of all arguments.
Category
Logical
Syntax
XOR(logical1, [logical2], ...)
Parameter | Description |
---|---|
logical |
A logical value or expression that can be evaluated as TRUE or FALSE. |
logicalN |
Optional. Additional logical values or expressions to evaluate. |
Behavior
The XOR
function is a logical function used to return a TRUE value if the number of TRUE inputs is odd, and a FALSE value if the number of TRUE inputs is even. Here's how it handles different kinds of inputs:
- Empty Cells: Empty cells in the arguments are ignored by the
XOR
function. - Text: If a text value is passed as an argument, it is treated as FALSE unless explicitly converted into a logical expression.
- Booleans:
XOR
function expects logical values (TRUE or FALSE) as arguments. If Boolean values are provided, they are processed as intended. - Numbers: Any numeric value other than zero is treated as TRUE. Zero is treated as FALSE.
- Errors: If any of the arguments are error values, the
XOR
function will return an error.
Common Errors
Error | Description |
---|---|
#VALUE! | This error occurs when the provided arguments are non-logical values. While XOR can handle numbers and text, other non-logical values will cause a #VALUE! error. |
#N/A | occurs when the function cannot evaluate any arguments into logical values. |
Best practices
- Use the
XOR
function when you need to check if an odd number of conditions are TRUE.- Always ensure that the arguments provided are either logical values (TRUE or FALSE), numbers (where 0 is FALSE and any other number is TRUE), or text (which is treated as FALSE). Providing other non-logical values will result in an error.
- Be aware that
XOR
ignores empty cells. If you want empty cells to be treated as FALSE, you'll need to handle this in your formula.- When using ranges, ensure they contain logical values. If no logical values are found, the function will return an error.
Usage
A few examples using the XOR function.
XOR(TRUE, FALSE) → TRUE // One TRUE
XOR(TRUE, TRUE) → FALSE // Two TRUEs cancel out
XOR(FALSE, FALSE, TRUE) → TRUE // Odd number of TRUEs
XOR(1, 0, TRUE) → FALSE // 1 treated as TRUE, 0 as FALSE → 2 TRUEs = FALSE
XOR("text", FALSE) → FALSE // "text" coerced as FALSE
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('MzQ5YzIzNzc3MTY0NThiZThlMzI0Nzk3MGY3OGM1N2Q5MGQ5OGNmYWU5YWY3YjlhNTZjN2MwZjc2YzhjZTY5ODg2NDk3OTVmYmE0ZGViZjllMzg5MGI4ZmI5MmI5MzQ2MjBiZWFkZjI3ZDY3Nzc0N2Y2MmNhNzM3MTdlMjI4MjQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOakV4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Task 1",
"Task 2",
"Task 3",
"Only One Complete?"
],
[
true,
false,
false,
"=XOR(A2,B2,C2)"
],
[
false,
true,
false,
"=XOR(A3,B3,C3)"
],
[
true,
true,
false,
"=XOR(A4,B4,C4)"
],
[
false,
false,
false,
"=XOR(A5,B5,C5)"
]
]
}]
});
</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('MzQ5YzIzNzc3MTY0NThiZThlMzI0Nzk3MGY3OGM1N2Q5MGQ5OGNmYWU5YWY3YjlhNTZjN2MwZjc2YzhjZTY5ODg2NDk3OTVmYmE0ZGViZjllMzg5MGI4ZmI5MmI5MzQ2MjBiZWFkZjI3ZDY3Nzc0N2Y2MmNhNzM3MTdlMjI4MjQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOakV4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Task 1",
"Task 2",
"Task 3",
"Only One Complete?"
],
[
true,
false,
false,
"=XOR(A2,B2,C2)"
],
[
false,
true,
false,
"=XOR(A3,B3,C3)"
],
[
true,
true,
false,
"=XOR(A4,B4,C4)"
],
[
false,
false,
false,
"=XOR(A5,B5,C5)"
]
];
// 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('MzQ5YzIzNzc3MTY0NThiZThlMzI0Nzk3MGY3OGM1N2Q5MGQ5OGNmYWU5YWY3YjlhNTZjN2MwZjc2YzhjZTY5ODg2NDk3OTVmYmE0ZGViZjllMzg5MGI4ZmI5MmI5MzQ2MjBiZWFkZjI3ZDY3Nzc0N2Y2MmNhNzM3MTdlMjI4MjQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOakV4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Task 1",
"Task 2",
"Task 3",
"Only One Complete?"
],
[
true,
false,
false,
"=XOR(A2,B2,C2)"
],
[
false,
true,
false,
"=XOR(A3,B3,C3)"
],
[
true,
true,
false,
"=XOR(A4,B4,C4)"
],
[
false,
false,
false,
"=XOR(A5,B5,C5)"
]
]
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('MzQ5YzIzNzc3MTY0NThiZThlMzI0Nzk3MGY3OGM1N2Q5MGQ5OGNmYWU5YWY3YjlhNTZjN2MwZjc2YzhjZTY5ODg2NDk3OTVmYmE0ZGViZjllMzg5MGI4ZmI5MmI5MzQ2MjBiZWFkZjI3ZDY3Nzc0N2Y2MmNhNzM3MTdlMjI4MjQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdOakV4TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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: [
[
"Task 1",
"Task 2",
"Task 3",
"Only One Complete?"
],
[
true,
false,
false,
"=XOR(A2,B2,C2)"
],
[
false,
true,
false,
"=XOR(A3,B3,C3)"
],
[
true,
true,
false,
"=XOR(A4,B4,C4)"
],
[
false,
false,
false,
"=XOR(A5,B5,C5)"
]
]
}]
});
}
}