9

IFERROR works great because it shortened the formula significantly by having a default behavior, returning the results if condition is not met (no error). I'm looking for the IFBLANK version of this behavior, i.e. return results if condition is not met (not blank).

Let say I have a complex multi lines formula as "Core Formula". I can use =IFERROR(Core Formula, "") to deal with errors.

I want something like =IFBLANK(Core Formula, "").

I know I can do it with =IF(Core Formula="","",Core Formula), but if my "Core Formula" is really complex and lengthy, "if formula" doubles my length of formula and makes it extremely difficult to follow/trace/modify, especially when the formula needs to be reviewed by other contributors.

P.S. I always have to deal with data that comprise of zeros and blanks which have different meaning.

music2myear
  • 49,799
mrr010
  • 127

3 Answers3

7

Such function doesn't exist in Excel, you have two options here:

1. Let

LET function is available only newer versions of Excel 365, with that you can write this:

=LET(x,<core formula>,IF(x="","",x)

2. create your own user defined VBA function

Open a VBA editor and type this code:

Function IFBLANK(param1 As Variable, param2 As Variable) As Variable
  If param1 = "" or param1 Is Null then
    Return param2
  Else 
    Return param1
  End If
End Function
Máté Juhász
  • 22,071
4

If you have a recent version of Excel that support the LAMBDA function, you can create your own by going into the "Name Manager" and creating an "IfBlank" entry with the following code:

=LAMBDA(value,default,IF(ISBLANK(value),default,value))

After that, you can call it from anywhere in your workbook.

=IfBlank(A1,A2)
Iceman
  • 41
-1

If the answer is blank excel will treat the contents differently than if it was text.

An acceptable solution and one that I use all the time is to simply add a &"" at the end of the formula. This will force excel to treat a blank as text and you won't see the zero that you typically see.

See this question. Display Blank when Referencing Blank Cell in Excel 2010

wbeard52
  • 3,483