AND function
PRO
BASIC
The AND
function in Jspreadsheet Formulas Pro is a logical operator that checks if all conditions within a formula are met. It will return TRUE only if every argument or condition is TRUE. However, if even a single argument or condition is FALSE, the function will return FALSE. This function is useful when you need to verify multiple conditions at the same time in your spreadsheet.
Documentation
Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments evaluates to FALSE.
Category
Logical
Syntax
AND(logical1, [logical2], ...)
Parameter | Description |
---|---|
logical1 |
The first condition to test. |
logicalN |
Optional. Additional conditions to test. You can specify up to 255 conditions. |
Behavior
The AND
function is a logical function used to test multiple conditions at the same time. It will return TRUE
if all the conditions are met and FALSE
if any of the conditions is not met.
- Empty cells: If an empty cell is referenced in the
AND
function, it is treated as zero (0
) and hence, asFALSE
. - Text: Text is not valid input for the
AND
function. If a cell containing text is referenced, the function will return a#VALUE!
error. - Booleans: The
AND
function directly accepts boolean (TRUE
orFALSE
) values as its arguments. - Errors: If any cell referenced by the
AND
function contains an error, the function will return that error.
Common Errors
Error | Description |
---|---|
#VALUE! |
This error occurs when one or more of the arguments to the AND function is text. |
#REF! |
This error is returned when a cell reference is not valid. This can occur if a cell is deleted. |
Best practices
- When using the
AND
function, ensure that the arguments are either boolean values or cell references that contain numeric values.- Avoid including cells that may contain text as they will result in a
#VALUE!
error.- Ensure to handle empty cells as they are treated as
FALSE
.- Be careful while deleting cells. If a cell referenced in the
AND
function is deleted, it returns a#REF!
error.
Usage
A few examples using the AND function.
AND(TRUE, TRUE) returns TRUE
AND(5=5, 3>2) returns TRUE
AND(FALSE, 1=1) returns 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('YmYxZWMyNDUyNzE0MmE3Y2YxNWIyZmVkMzc0ZjA5OTgzMjVkMmQ1OWExNTVkOWI0NzE2YmEyYWQyOTk4YjlhZTA3YWMyNDlmM2U5MmM0MjYwNmEyYjg1Njk1ZGVmNjFkZjRjNTU0NzkzZjg2MzJkZGM2MjYwOWU5YjQ3YTkwOTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pOVGcwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Student",
"Math Score",
"English Score",
"Passed Both"
],
[
"Alice",
85,
78,
"=AND(B2>=70, C2>=70)"
],
[
"Bob",
65,
82,
"=AND(B3>=70, C3>=70)"
],
[
"Carol",
92,
88,
"=AND(B4>=70, C4>=70)"
],
[
"David",
73,
69,
"=AND(B5>=70, C5>=70)"
]
]
}]
});
</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('YmYxZWMyNDUyNzE0MmE3Y2YxNWIyZmVkMzc0ZjA5OTgzMjVkMmQ1OWExNTVkOWI0NzE2YmEyYWQyOTk4YjlhZTA3YWMyNDlmM2U5MmM0MjYwNmEyYjg1Njk1ZGVmNjFkZjRjNTU0NzkzZjg2MzJkZGM2MjYwOWU5YjQ3YTkwOTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pOVGcwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Student",
"Math Score",
"English Score",
"Passed Both"
],
[
"Alice",
85,
78,
"=AND(B2>=70, C2>=70)"
],
[
"Bob",
65,
82,
"=AND(B3>=70, C3>=70)"
],
[
"Carol",
92,
88,
"=AND(B4>=70, C4>=70)"
],
[
"David",
73,
69,
"=AND(B5>=70, C5>=70)"
]
];
// 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('YmYxZWMyNDUyNzE0MmE3Y2YxNWIyZmVkMzc0ZjA5OTgzMjVkMmQ1OWExNTVkOWI0NzE2YmEyYWQyOTk4YjlhZTA3YWMyNDlmM2U5MmM0MjYwNmEyYjg1Njk1ZGVmNjFkZjRjNTU0NzkzZjg2MzJkZGM2MjYwOWU5YjQ3YTkwOTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pOVGcwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Student",
"Math Score",
"English Score",
"Passed Both"
],
[
"Alice",
85,
78,
"=AND(B2>=70, C2>=70)"
],
[
"Bob",
65,
82,
"=AND(B3>=70, C3>=70)"
],
[
"Carol",
92,
88,
"=AND(B4>=70, C4>=70)"
],
[
"David",
73,
69,
"=AND(B5>=70, C5>=70)"
]
]
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('YmYxZWMyNDUyNzE0MmE3Y2YxNWIyZmVkMzc0ZjA5OTgzMjVkMmQ1OWExNTVkOWI0NzE2YmEyYWQyOTk4YjlhZTA3YWMyNDlmM2U5MmM0MjYwNmEyYjg1Njk1ZGVmNjFkZjRjNTU0NzkzZjg2MzJkZGM2MjYwOWU5YjQ3YTkwOTksZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01EY3pOVGcwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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: [
[
"Student",
"Math Score",
"English Score",
"Passed Both"
],
[
"Alice",
85,
78,
"=AND(B2>=70, C2>=70)"
],
[
"Bob",
65,
82,
"=AND(B3>=70, C3>=70)"
],
[
"Carol",
92,
88,
"=AND(B4>=70, C4>=70)"
],
[
"David",
73,
69,
"=AND(B5>=70, C5>=70)"
]
]
}]
});
}
}