6

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.

ZygD
  • 2,459
  • 12
  • 26
  • 43
mrr010
  • 97
  • 1
  • 8
  • You could try to tackle this problem at the source of the data. I can imagine that you are referencing the data, but if your cells are empty they may return an unwanted zero. Is that what you are dealing with? Instead of referencing directly, put your data inside an `IF()` >>> `IF(A1:B4="","",A1:B4)` instead of a normal range reference. Don't forget that this makes it a CSE-entered formula. – JvdV Mar 02 '21 at 08:23
  • The core formula is the complex part used to return the range I'm looking for which is dynamic, depends on some user input. I don't want to use =IF(Core Formula="","",Core Formula). – mrr010 Mar 03 '21 at 03:52
  • +1 for builtin `IFBLANK` in excel – juanmirocks Oct 25 '22 at 09:43

3 Answers3

5

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

Let

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

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

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
  • 21,403
  • 6
  • 54
  • 73
  • Thanks, VBA should be fine for the time being. Do you know if this function will be available in later excel development? – mrr010 Mar 02 '21 at 07:55
  • I don't know whether it's planned by Microsoft or not. My feeling is that there is not a big need for this function so probably won't be added. – Máté Juhász Mar 02 '21 at 09:54
3

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
  • 31
  • 5
0

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,417
  • 3
  • 28
  • 40
  • If I use ````&""```` , it will turn numbers into text. What if I have to sum those numbers? My range of cells contains numbers, zeros, text and blanks. I was able to use ````SUM````. Now I can't. – mrr010 Mar 12 '21 at 09:01