Documentation/Calc Functions/AND

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

Other languages:

Calc Functions Home | List of functions | Abbreviations

Function name:

AND

Category:

Logical

Summary:

Tests multiple conditions to determine whether all are met. TRUE is returned if all conditions are met; otherwise FALSE is returned.

Syntax:

AND(Logical Value 1 [; Logical Value 2 [; … [; Logical Value 255]]])

Returns:

Returns the logical value TRUE if all supplied conditions are met. Returns the logical value FALSE when one or more of the supplied conditions are not met. In other words, the function returns the logical value FALSE if at least one of the supplied conditions is not met.

Arguments:

Logical Value 1, Logical Value 2, … , Logical Value 255 give the set of conditions to be checked. Each condition should comprise an expression that evaluates to give a logical value that is either TRUE or FALSE. If an expression evaluates to a number instead of a logical value, then the value 0 (zero) is treated as FALSE and all non-zero numeric values are treated as TRUE. Each argument may take one of the following forms:

A logical value, or an expression that can be evaluated to a logical value. Examples include TRUE, 3, 1<5, 2+3=7, A2*A3, and B8<10.
A reference to a single cell containing a logical value or expression.
A reference to a cell range containing logical values or expressions (for example, D6:E12 or A1:B9~E12:E14).
The name of a named range, comprising cells containing logical values or expressions.
The name of a database range, comprising cells containing logical values or expressions.
An inline array of logical values or numbers (for example {TRUE, FALSE, 0, 123.456}).

Note that although AND can accept up to 255 arguments, each argument could specify a range of cells. This means that the number of conditions processed could be many more than 255.

If the arguments do not give at least one logical value, then AND reports a #VALUE! error.
If the arguments give exactly one value, then AND returns that logical value.
If any argument is a string in quotation marks, then AND reports a #VALUE! error. This does not apply to references to cells containing strings.
Empty cells and cells that contain text are ignored by AND.
If a cell contains an error value, then AND propagates that error, irrespective of the content of other cells.

Additional details:

Details specific to AND function

In its simplest form, the logical AND (sometimes known as logical conjunction) of two logical values is an operation that produces a value of TRUE if both its operands are true; otherwise the result is FALSE. It can be expressed as a truth table as follows:

Truth table for logical AND operation
Operand 1	Operand 2	Operand 1 AND Operand 2
true	true	true
true	false	false
false	true	false
false	false	false

AND ignores any text in cells or empty cells. If you suspect wrong results, look for text within the supplied data range(s). To highlight text content within a data range, use Calc's View ▸ Value Highlighting feature.
Although you can use AND on its own, it is often much more useful when combined with other functions. For example, consider a value in cell A1 that you expect to be in the range 1 to 10. You might place a formula such as =IF(AND(A1>=1; A1<=10); "In range"; "Out of range") in some other cell to check whether a value in cell A1 is within the expected range.

General information about Calc's logical functions

Note:
For convenience, the information in this subsection is repeated on all pages describing Calc’s logical functions.

Calc uses the numbers 0 (FALSE) and 1 (TRUE) to represent logical values. For example, enter 1 in a cell that has the default general number format. Then select Format ▸ Cells on the Menu bar to display the Format Cells dialog, select the Boolean Value option in the Category area of the Numbers tab, and click the OK button. Notice that the number 1 is now displayed as TRUE. To revert to displaying the number 1, repeat the process but select Number instead of Boolean Value.

To enter a logical value into a cell, simply type either TRUE or FALSE (case-insensitive) into the cell. Calc will recognize the logical value, display it in uppercase letters, and change the cell's format to that appropriate to Boolean values.

Calc functions that produce a logical result return a number, either 0 or 1. In the case of a formula that simply calls such a logical function and is in a cell that has the default general number format, the cell is switched to the Boolean value format and the returned value is displayed as FALSE or TRUE. For example =TRUE() returns the value 1, which is displayed as TRUE; if you then change the format to a numerical format, it is displayed as the number 1.

Calc functions that test for a logical result always evaluate the expression to be tested and check if it is equal to 0. The value 0 is taken as FALSE and any other value is taken as TRUE. For example, =NOT(0) returns TRUE and =NOT(57.89) returns FALSE.

In contrast to Calc, Microsoft Excel has a separate type for logical values - they are not numbers but are sometimes converted to numbers. Take care therefore if you need compatibility.

Examples:

Data for examples
	A	C	D
1	=TRUE()	45	2.2
2	=NOT(FALSE())		3
3	1		-5.4
4	2
5	3	"Conditions": `A1:A5`	⇐ `named range`

Formula	Description	Returns
=AND(FALSE(); TRUE())	One of the arguments is not true.	FALSE
=AND(TRUE(); C1>10; FALSE())	One of the arguments is not true.	FALSE
=AND(D1:D3)	All the supplied numeric values are not equal to 0 (zero).	TRUE
=AND({2; 4; 6; 8})	All the supplied numeric values are not equal to 0 (zero).	TRUE
=AND(12<13; 14>12; 7<6)	The third argument is false.	FALSE
=AND(Conditions)	All cells in the named range are either TRUE or not 0 (zero).	TRUE

Related LibreOffice functions:

ODF standard:

Section 6.15.2, part 2

Related (or similar) Excel functions:

AND