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

Lookup and Reference Examples

  • How to use Excel MATCH Function
  • How to create dynamic named range with OFFSET in Excel
  • Excel Advanced Lookup using Index and Match Functions
  • How to get address of first cell in range in Excel
  • Last row number in range

Data Analysis Examples

  • How to Create Column Chart in Excel
  • How to Use Solver Tool in Excel
  • Managing Conditional Formatting Rules in Excel
  • How To Insert and Customize Sparklines in Excel
  • How to count table rows in Excel

Data Validation Examples

  • Excel Data validation exists in list
  • Excel Data validation don’t exceed total
  • Excel Data validation must not contain
  • Data validation must not exist in list
  • Excel Data validation allow uppercase only

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:   If this AND that 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:   How to check cell contains which things in Excel

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:   Get work hours between dates and times 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 count specific characters in a cell in Excel
  • How to convert text string to array in Excel
  • How to count specific characters in a range in Excel
  • TEXT function: Description, Usage, Syntax, Examples and Explanation
  • Excel MAX, MATCH and ADDRESS function illustration
  • How to get year from date in Excel
  • Roll back weekday to Friday base on a particular date in Excel
  • How to calculate next scheduled event in Excel
  • How to calculate nth day of year in Excel
  • Get work hours between dates and times in Excel
  • PMT function: Description, Usage, Syntax, Examples and Explanation
  • CUMIPMT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate periods for annuity in Excel
  • FV function: Description, Usage, Syntax, Examples and Explanation
  • PMT, RATE, NPER, PV and FV Financial Functions in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles