Exceljet

Quick, clean, and to the point

Conditional message with REPT function

Excel formula: Conditional message with REPT function
Generic formula 
=REPT("message",logical test)
Explanation 

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.

How this formula works

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

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.