Skip to main content

๐Ÿงฎ Mathematical, Relational, and Logical Operators

Building Complex Expressions in Spreadsheets

Operators are symbols that specify the type of calculation to perform in a formula. Understanding the different types of operators and how they work together is essential for creating effective spreadsheet calculations and conditions.

๐Ÿ”ข Mathematical Operatorsโ€‹

Mathematical operators perform basic arithmetic calculations on numbers.

โž• Addition (+)โ€‹

  • Adds two numbers together
  • Syntax: =A1+B1 or =5+10
  • Example: =SUM(A1:A10)+B5 adds the sum of range A1:A10 to the value in B5
  • Can also concatenate text strings (though & is preferred)

โž– Subtraction (-)โ€‹

  • Subtracts the second number from the first
  • Syntax: =A1-B1 or =20-5
  • Example: =COUNT(A1:A10)-COUNT(B1:B10) finds the difference between two counts
  • Can also be used as a negative sign: =-A1

โœ–๏ธ Multiplication (*)โ€‹

  • Multiplies two numbers together
  • Syntax: =A1*B1 or =5*10
  • Example: =A1*0.15 calculates 15% of the value in A1
  • Use parentheses to control order: =(A1+B1)*C1

โž— Division (/)โ€‹

  • Divides the first number by the second
  • Syntax: =A1/B1 or =20/4
  • Example: =SUM(A1:A10)/COUNT(A1:A10) calculates the average manually
  • Returns #DIV/0! error if dividing by zero

๐Ÿ“ˆ Exponentiation (^)โ€‹

  • Raises a number to a power
  • Syntax: =A1^B1 or =5^2
  • Example:
=4^0.5  // Square root of 4
=10^3   // 10 cubed (1000)

๐Ÿ’ฏ Percentage (%)โ€‹

  • Converts a number to a percentage (divides by 100)
  • Syntax: =A1*10% or =50%
  • Example:
=A1*B1%  // B1 percent of A1
=50%     // Equals 0.5

๐Ÿ” Relational Operatorsโ€‹

Relational operators compare two values and return a logical value (TRUE or FALSE).

๐ŸŸฐ Equal To (=)โ€‹

  • Tests if two values are equal
  • Syntax: =A1=B1 or =A1="Text"
  • Example:
=IF(A1=10,"Yes","No")  // Returns "Yes" if A1 equals 10
  • Case-insensitive for text comparisons in most spreadsheets

โ‰  Not Equal To (<>)โ€‹

  • Tests if two values are not equal
  • Syntax: =A1&lt;&gt;B1 or =A1&lt;&gt;"Text"
  • Example:
=COUNTIF(A1:A10,"<>0")  // Counts non-zero values

๐Ÿ“ˆ Greater Than (>)โ€‹

  • Tests if the first value is greater than the second
  • Syntax: =A1&gt;B1 or =A1&gt;10
  • Example:
=IF(A1>B1,"Above","Below or Equal")  // Compares two cells

๐Ÿ“‰ Less Than (<)โ€‹

  • Tests if the first value is less than the second
  • Syntax: =A1&lt;B1 or =A1&lt;10
  • Example:
=COUNTIF(A1:A10,"<0")  // Counts negative values

๐Ÿ“Š Greater Than or Equal To (>=)โ€‹

  • Tests if the first value is greater than or equal to the second
  • Syntax: =A1&gt;=B1 or =A1&gt;=10
  • Example:
=IF(A1>=70,"Pass","Fail")  // Simple grading rule

๐Ÿ“Š Less Than or Equal To (<=)โ€‹

  • Tests if the first value is less than or equal to the second
  • Syntax: =A1&lt;=B1 or =A1&lt;=10
  • Example:
=SUMIF(A1:A10,"<=0",B1:B10)  // Sums values in B where A is negative or zero

๐Ÿงฉ Logical Operatorsโ€‹

Logical operators combine or modify logical values (TRUE/FALSE).

๐Ÿ”— ANDโ€‹

  • Returns TRUE if all conditions are true
  • Syntax: =AND(logical1, logical2, ...)
  • Example:
=AND(A1>10, A1<20)  // TRUE if A1 is between 10 and 20
=IF(AND(A1>=60, B1>=60), "Pass", "Fail")  // Requires passing both subjects

๐Ÿ”€ ORโ€‹

  • Returns TRUE if any condition is true
  • Syntax: =OR(logical1, logical2, ...)
  • Example:
=OR(A1="Yes", B1="Yes")  // TRUE if either cell contains "Yes"
=IF(OR(A1<0, A1>100), "Invalid", "Valid")  // Checks if value is out of range

๐Ÿ”„ NOTโ€‹

  • Reverses a logical value (TRUE becomes FALSE, FALSE becomes TRUE)
  • Syntax: =NOT(logical)
  • Example:
=NOT(A1>10)  // Same as A1<=10
=IF(NOT(ISBLANK(A1)), "Filled", "Empty")  // Checks if cell is not empty

๐Ÿ”ข Operator Precedenceโ€‹

When a formula contains multiple operators, they are evaluated in a specific order:

  1. ๐Ÿ“Œ Parentheses ()
  2. ๐Ÿ“ˆ Exponentiation ^
  3. โœ–๏ธ Multiplication * and Division / (from left to right)
  4. โž• Addition + and Subtraction - (from left to right)
  5. ๐Ÿ” Relational operators =, &lt;&gt;, &lt;, &lt;=, &gt;, &gt;=
  6. ๐Ÿงฉ Logical operators: NOT, AND, OR

Examples of Precedenceโ€‹

=5+2*3         // Equals 11 (multiplication before addition)
=(5+2)*3       // Equals 21 (parentheses override default precedence)
=5&gt;2*3         // Equals FALSE (multiplication before comparison)
=NOT(A1&gt;5 AND A1&lt;10)  // Equals NOT(A1&gt;5) OR NOT(A1&lt;10) by De Morgan's Law

๐Ÿงช Combining Operators in Complex Formulasโ€‹

๐Ÿ”„ Nested Conditionsโ€‹

  • Use parentheses to group conditions
  • Example:
=IF(AND(A1>=60, OR(B1>=70, C1>=80)), "Pass", "Fail")
  • This checks if A1 is at least 60 AND either B1 is at least 70 OR C1 is at least 80

๐Ÿงฎ Mathematical and Logical Combinationsโ€‹

  • Example:
=IF((A1+B1)/2>60, "Pass", "Fail")
  • This calculates the average of A1 and B1, then compares it to 60

๐Ÿ“ Text and Numeric Operationsโ€‹

  • Example:
=IF(A1>100, "High: " & A1, "Normal: " & A1)
  • This combines text with the value from A1 based on a condition

โœ… Best Practicesโ€‹

  • Use parentheses to make the order of operations clear, even when not strictly necessary
  • Break complex formulas into smaller steps using intermediate cells:
// Instead of this complex formula:
=IF(AND(A1>B1, C1<D1), (A1+C1)/(B1+D1), A1*C1)

// Use intermediate cells:
E1: =A1>B1
F1: =C1<D1
G1: =(A1+C1)/(B1+D1)
H1: =A1*C1
I1: =IF(AND(E1,F1), G1, H1)
  • Test complex conditions with simple values to verify they work as expected
  • Use cell comments to explain complex logical expressions
  • Consider using named ranges to make formulas more readable:
// Instead of:
=IF(AND(A1>B1, C1<D1), E1, F1)

// Use named ranges:
=IF(AND(Revenue>Expenses, CurrentYear<PreviousYear), Profit, Loss)

Understanding operators and how they interact is fundamental to creating powerful spreadsheet solutions that can handle complex calculations and decision-making processes.