Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to create Gauge Chart in Excel
  • How to calculate current stock or inventory in Excel
  • How to Create Gantt Chart in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel

References

  • Offset in Excel
  • How to use Excel LOOKUP Function
  • How to get first row number in range in Excel
  • Vlookup Examples in Excel
  • How to get last column number in range in Excel

Data Validations

  • Excel Data validation only dates between
  • How To Create Drop-down List in Excel
  • Excel Data validation no punctuation
  • Excel Data validation number multiple 100
  • Excel Data validation date in specific year

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:   Win loss points calculation 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:   If cell is this OR that 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:   How to get last word in a cell in Excel

Post navigation

Previous Post:

Get day from date in Excel

Next Post:

Calculate loan interest in given year 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

Logical Functions

  • OR function: Description, Usage, Syntax, Examples and Explanation
  • Extract multiple matches into separate rows in Excel
  • IF, AND, OR and NOT Functions Examples in Excel
  • How to return blank in place of #DIV/0! error in Excel
  • IF with boolean logic in Excel

Date Time

  • How to calculate Next working/business day in Excel
  • Add workdays to date custom weekends in Excel
  • Get last day of month in Excel
  • Convert decimal hours to Excel time
  • Display Days in month in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • Running count group by n size in Excel
  • Categorize text with keywords in Excel
  • Group arbitrary text values in Excel
  • Group times into unequal buckets in Excel

General

  • How to add sequential row numbers to a set of data in Excel
  • Mark Workbook as Final in Excel
  • How to password protect excel sheet?
  • Freeze and Unfreeze Panes in Excel
  • Check if multiple cells have same value with case sensitive in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning