I use the product of the logical test and the value_if_true instead of using an IF function when the following two conditions are satisfied:

  1. value_if_true is a number
  2. value_if_false is zero

When the conditions are met test*value_if_true is equivalent to IF(test,value_if_true,if_false)


   IF Function      test * if_true
  =IF(TRUE,4,0)  |  =TRUE*4  
  =4             |  =4       
  =IF(FALSE,4,0) |  =FALSE*4
  =0             |  =0

Adapt for text

I want to apply a similar approach but change the value_if_true from a number to a text string.

The two conditions would now be:

  1. value_if_true is a text string
  2. value_if_false is zero.

The formula test*if_true doesn't work in this case:

=TRUE*"text"     =FALSE*"text"
=#VALUE!         =#VALUE!

Is there a way to adapt the formula so that it can accept values for value_if_true that are text strings?

I want to use it in long complex formula with both text and numbers and want to make combinations using * and + instead of AND and OR

  • What do you think you'd get if you multiplied "dog" by 4? Four dogs? in integer test*value == >0 or 0, which can be used as true/false. dog*4==NaN.
    – Tetsujin
    Feb 8, 2022 at 16:38
  • Why reinvent the wheel? Any clever solutions to avoid using IF will require a lot more characters than the very few characters you saved by avoiding it. This seems like an The XY Problem that begs for additional details on the "why"
    – Blindspots
    Jul 6 at 2:16

1 Answer 1


without a multiplication:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.