Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Excel Advanced Lookup using Index and Match Functions
  • Lookup entire row in Excel
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • Offset in Excel

Data Analysis Examples

  • Conflicting Multiple Conditional Formatting Rules in Excel
  • Data Series in Excel
  • How to perform a t-Test in Excel
  • How To Create Frequency Distribution in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS

Data Validation Examples

  • Excel Data validation unique values only
  • Excel Data validation must begin with
  • Data validation must not exist in list
  • Excel Data validation with conditional list
  • How To Create Drop-down List in Excel

How to display conditional message with REPT function in Excel

by

To display a conditional message, without the IF function, you can use boolean logic and the REPT function.

Formula

=REPT("message",logical test)

Explanation

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

Worked Example:   How to calculate next scheduled event in Excel

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.

Worked Example:   Excel Data validation with conditional list

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

Worked Example:   OR function Examples in Excel

Post navigation

Previous Post:

How to count line breaks in cell in Excel worksheet

Next Post:

How to check cell contains which things in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to extract word containing specific text in Excel
  • TEXTJOIN function: Description, Usage, Syntax, Examples and Explanation
  • How to split text with delimiter in Excel
  • Remove line breaks in a cell in Excel
  • BAHTTEXT function: Description, Usage, Syntax, Examples and Explanation
  • Count holidays between two dates in Excel
  • Calculate expiration date in Excel
  • Get project midpoint in Excel
  • Extract date from a date and time in Excel
  • Add workdays to date custom weekends in Excel
  • COUPDAYBS function: Description, Usage, Syntax, Examples and Explanation
  • CUMIPMT function: Description, Usage, Syntax, Examples and Explanation
  • ACCRINTM function: Description, Usage, Syntax, Examples and Explanation
  • Example of payment for annuity in Excel
  • FVSCHEDULE function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles