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 (–).
Explanation
Normally, you want to maintain numeric values in Excel, because they can be used in formulas that perform numeric calculations. However, there are situations where converting numbers to text makes sense. One example is when you want to concatenate (join) a formatted number to text. For example, “Sales last year increased by over 15%”, where the number .15 has been formatted with a percent symbol. Without the TEXT function, the number formatting will be stripped. Another example is when you want to perform a lookup on numbers using wildcards , which can’t be done with numeric values.
Convert with formatting
The TEXT function can be used to convert numbers to text using a given number format . In the example shown, the TEXT function is used to convert each number in column B to a text value using the formula and number shown in column F. The TEXT function accepts a number as the value argument, the number format to use as the format_text argument. The number format can include codes for dates, times, numbers, currency, percentage, and so on, as well as custom number formats . Notice the output from the TEXT function is a text value, which is left-aligned by default in Excel.
Convert without formatting
To convert a number in A1 to a text value without number formatting, you can concatenate the number to an empty string ("") like this:
=A1&"" // convert A1 to text
With the number 100 in cell A1, the result is “100”.
