Spreadsheet: Operators
MedCalc divides the operators in the following groups:
- Arithmetic operators: arithmetic operators are used with numeric values to perform common arithmetical operations, such as addition, subtraction, multiplication etc.
- Comparison Operators: comparison operators compare two numbers or two strings. The result is a boolean value expressed as a number, either 1 (=TRUE), or 0 (=FALSE).
- Unary operators: operators that act upon a single value to produce a new value.
- Logical operators: logical operators operate on boolean expressions and values.
Operators in order of precedence
^ | Exponentiation | Arithmetic |
- | Unary minus | Unary |
+ | Unary plus | Unary |
! | Not | Unary |
* | Multiplication | Arithmetic |
/ | Division | Arithmetic |
+ | Addition | Arithmetic |
- | Subtraction | Arithmetic |
= | Equal | Comparison |
<> | Not equal | Comparison |
!= | Not equal | Comparison |
< | Less than | Comparison |
> | Greater than | Comparison |
<= | Less than or equal to | Comparison |
>= | Greater than or equal to | Comparison |
&& | And | Logical |
|| | Or | Logical |
Order of operators
- The order of operations can be changed using parentheses. Operations within parentheses are performed first.
- The exponentiation operator ^ is left-associative. In the expression 2^3^2, 2^3 is evaluated first. 2^3^2 is (2^3)^2 (=64), not 2^(3^2) (=512).
- When arithmetic and comparison operators are combined in one expression, the arithmetic are performed first.
- The not operator ! changes a FALSE value into TRUE (=1), and a TRUE value into FALSE (=0). Zero values or empty strings are evaluated to FALSE.
- !0 results in 1
- !1 results in 0
- !5 results in 0
- !"" results in 1 (an emptry strings is evaluated to FALSE)
- !"MedCalc" results in 0
- MedCalc may drop superfluous unary minus or plus signs (for example -0 is 0, and +3 is 3).
Examples
- -2^3 returns -8 (the power operator has precedence over the unary minus).
- SQRT(9)=3 returns 1 (TRUE).
- B6<5+3 returns 1 if the contents of B6 is less than 8, otherwise this expression returns 0.
- 5+3*(A1>3) returns 8 if cell A1 contains a value greater than 3 (since A1>3 = TRUE = 1). If A1 contains 3 or a number less than 3, this expression returns 5 (A1>3 = FALSE = 0).