A simple function to replace the IF(A1="","",EnterFormulaHere)
Everyone down to amateur excel users knows the quick fix for displaying no value in a cell if it's precedent is blank is the =IF(A1="","",EnterFormulaHere). Usually used when the end user hasn't yet entered information into input cells and Errors in cells are undesired.
This gets more complex when there are many precedent cells and requires this =IF(OR(A1="",B2="",C3=""),"",EnterFormulaHere) etc.
We need a function for this such as:
So, =IFBLANK(A1*B2*C3) would only return the result of the formula if none of cells A1, B2 and C3 were blank.
Or, =IFBLANK(A1*B2*C3,(A1,B2,D7)) allows the user to select the specific cells to be checked rather than just what cells are being used in the formula. So the result would only be displayed if none of cells A1, B2 and D7 are blank.
Or, =IFBLANK(A1*B2*C3,,ALL) checks to see if all the cells are blank (Similar to using the AND function in an IF statement), whereas =IFBLANK(A1*B2*C3,,ANY) checks to see if any of the cells are blank (Similar to using the OR function in an IF statement)
Interesting suggestion Jon, thanks for posting it. There’s some other suggestions around blank/null, so we’ll be taking a look at the space in general, and making particularly sure that we consider related functions that get a lot of votes. As always, the more votes something gets the more chance that a specific request gets in the execution pipe sooner. So I just want to encourage folks to keep voting for the ideas they like best!
John [MS XL]
Ryan MacGregor commented
I'd like to down-vote this. Referencing blank cells in formulas is generally poor practice. Too easy to accidentally delete a blank cell's row or column, which would produce a #REF! error in the formula referencing that cell. Use zeros for inputs rather than leave input cells blank.