Summary

To display a conditional message, without the IF function, you can use boolean logic and the REPT function. In the example shown, the formula in D5 (copied down) is:

=REPT("low",C5<100)

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.

Generic formula

=REPT("message",logical test)

Explanation 

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:

=REPT("low",C5<100)
=REPT("low",TRUE)
=REPT("low",1)
="low"

In other words, if C5 < 100, output "low" 1 time. In cell C6, the formula is evaluated like this:

=REPT("low",C6<100)
=REPT("low",FALSE)
=REPT("low",0)
=""

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:

=IF(C5<100,"low","")

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:

=REPT("low",(C5<100)*(B5="Monday"))

The equivalent IF formula is:

=IF(C5<100,IF(B5="Monday","low",""),"")

or, simplifying a bit with AND:

=IF(AND(C5<100,B5="Monday"),"low","")

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 (--).

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.