Use Formulas for Basic Logical Judgment
When we design tables, we need to use logic in many cases. Is "income" greater than "expenditure"? Is "age" less than "18"? Does "progress" equal "100%"? ...
This article details how the data is compared in the formula.
Logical operators and functions
| Operator/Function | Description | Example |
|---|---|---|
| > | greater than | 3 > 2 => TRUE {Math grade} >{average grade} |
| < | less than | 2 < 3 => TRUE {Math grade} < {average grade} |
| >= | greater than or equal to | 3 >= 3 => TRUE {Math grade} >= {average grade} |
| <= | less than or equal to | 2 <= 2 => TRUE {Math grade} <= {average grade} |
| = | equal to | 2 = 2 => TRUE {Math grade} = {average grade} |
| != | is not equal to | 3 != 2 => TRUE {Math grade} != {average grade} |
| IF(logical, value1, value2) | Determines whether a condition is met, and returns the first value if it is met, and the second value if it is not. 【logical】is a logical condition that indicates that the result of a computation is true (true) or false (false). 【value1】 is the value returned when the logical condition is true. 【value2】 is the value returned when the logical condition is false. IF supports nested use and can be used to check if a cell is blank/empty. | IF({math score}> 60, "pass", "fail") IF({water temperature} > 40, IF({water temperature}) < 60, "Just right", "too hot"), "too cold") IF({start time} = BLANK(), "Please enter time", "time entered") |
Output result
After the logical operation of the formula, when the output result is "true" or "false", it is represented as "checked ✅" (true) and "not checked" (false) in the cell.
When participating in arithmetic operations, "✅ is checked" (true) represents 1; Unchecked (true) represents 0.
Greater than and greater than or equal to
In the Order Management table, we need to check whether the "profit per item" is greater than 0, that is, we can compare whether the "unit price" is greater than the "cost price". If it is greater than, it will return "checked" (true); if it is less than, it will return "unchecked" (false). Then the formula is written as: {unit price} > {cost price}
We can also directly judge whether "profit per product" is greater than or equal to 0, that is, the formula is written as: {profit per product} ≥ 0
Less than or equal to
In the Order Management table, if "gross margin" is less than 30%, it is "promotion", then the formula of "promotion" column is written as: {gross margin} < 0.3
If it is equal to 30%, it is also promotional money, then the formula is written as: {gross profit margin} ≤ 0.3!
Equal or not equal
In the Order Management table, if the value of Logistics is Confirm Shipment, the order has been shipped successfully. Then the formula of the column "whether it has been delivered" is written as: {logistics} = "confirm delivery"
If "Logistics" is not equivalent to "Confirm shipment", it indicates that the order needs to be shipped. Then the formula of "need to deliver" column is written as: {logistics}! = "shipment confirmed"
IF()
IF(logical, value1, value2) function can define the output value based on the logical judgment:
If true, the output value is value1
For false, the output value is value2
For example, in the Order Management table, IF "logistics" is "shipment confirmed", it indicates that the order has been successfully shipped, otherwise, it prompts for shipment, then the formula in the "shipped or not" column is written as: IF({logistics} = "shipment confirmed", "✅ shipped", "⏰ to be shipped")