Conditional message with REPT function
If the value in column C is less than 100, the formula returns "low". If not, the formula returns an empty string (""), which looks like a blank cell.
This formula uses boolean logic to output a conditional message. If the value in column C is less than 100, the formula returns "low". If not, the formula returns an empty string ("").
Boolean logic is a technique of handling TRUE and FALSE values like 1 and 0. In cell C5, the formula is evaluated like this:
In other words, if C5 < 100, output "low" 1 time. In cell C6, the formula is evaluated like this:
In other words, if C6 < 100 is FALSE, output "low" zero times.
IF function alternative
Conditional messages like this are more commonly handled with the IF function. With IF, the equivalent formula is:
Both formulas return exactly the same result, but the REPT version is a bit simpler.
Extending the logic
Boolean logic can be extended with simple math operations to handle more complex scenarios. Briefly, AND logic can be expressed with multiplication (*) OR logic can be expressed with addition (+). For example, to return "low" only when (count < 100) AND (day = Monday) we can use boolean logic like this:
The equivalent IF formula is:
or, simplifying a bit with AND:
Coercing TRUE and FALSE to 1 and zero
When using boolean logic, you'll sometimes need to force Excel to coerce TRUE and FALSE to 1 and zero. A simple way to do this is to use a double-negative (--).