Two new =if's
Just like =IFERROR(), could we also get =IFBLANK() and =IFZERO(). I would use these on a regular basis, and it would cut in half the size of large formulas where I just want something specific to happen where the result is either blank or zero
Thanks for the suggestion figboot. As always, we’ll prioritize this according to the number of votes it gets, so please be sure to vote it up if you want to see it done sooner.
John [MS XL]
surely it's a better idea to implement this:
IFX(LongFormula, Condition1, Value if True, Condition2, Value if True, ... ConditionN, Value if True)
but whatever's faster to implement to be honest!
this is a good idea (I vote for it) ... but the "universal if" is better:
because while IFBLANK() and IFZERO() would be useful, it wouldn't help when testing against ="" (which is different than ISBLANK() if there's a formula resulting in "" in the cell)...
and there are probably other use cases where we want the IF to use the result of the tested formula without having to duplicate it.
I've said this elsewhere, but a lot of the suggestions require a host of new formulae just to get around the problem of duplication. No one wants to write: IF(ISBLANK(big formula),"blank",big formula) - not only is it difficult to write in the first place but it create huge risks for maintenance. It also requires double the computing effort it should.
My preference is to permit naming of imbedded formula rather like lambda functions, for example:
IF(ISBLANK( big_formula:=big formula),"blank",big_formula)
where big_formula is the imbedded name and "big formula" represents a standard excel formula returning a value.
Everyone who voted for this should also vote for the IFX() generalised formula, so that it covers IFZERO(), IFBLANK(), IFNULL(), etc
It would also cover IF[anything], and thus entirely remove the need for doubling up the [long formula] bit.
Michael Bishop commented
I hope you're still considering the IfZero function. I know it doesn't have that many votes, but I'd use this function every hour of my spreadsheet work and would give it all the votes in the world if I could. Hopefully you agree that it's a compelling function.
Michael Bishop commented
I'd also really appreciate introduction of the IfZero function. It'd help me keep a cleaner and tighter spreadsheet.
I like Kenneth's last workaround except that it makes it harder to identify errors.
Not sure I understand why? Why wouldn't you just do formula=IF(A1="","yes","no"). If cell A1 is blank it returns a yes, if not you get no, of course you'd change the yes and no's to whatever you want to happen. Same formula for zero, just replace the "" with 0. Although maybe you've got something more complicated going on, but I'm not sure what the function would give you that you don't have already?
=IF(cell,"value if true","value if false") should work for you.
For cells that are either blank or have a date value I use this formula:
=IF(A2,"Closed","Not Closed"). Therefore, if cell A2 has a date value, it returns the result Closed. If cell A2 is empty, it returns the result Not Closed. Very quick calculation - takes a matter of seconds to calculate over 130,000 rows.
I imagine you could achieve the same result by using the same formula, but changing A2 to A2=0.
Just a general IFCONDITION() function would be fine, whether it be blank, zero, 42, whatever.
I do this often as well. Many times I'll have a complex formula that could result in an error and I need to trap for that error which causes me to enter an IF function with the formula that I am evaulating twice, just to show the result if there is no error.
Kenneth Barber commented
I'll support this suggestion by listing some workarounds that I do. Maybe they will help you in the meantime.
One workaround to shorten your formulas is this. Instead of IF(ISBLANK(big formula),"blank",big formula), keep your big formula in a helper column, so then your formula becomes IF(ISBLANK(A1),"blank",A1).
For a workaround for IFZERO, if your formula is IF(denominator=0,0,numerator/denominator), change it to IFERROR(numerator/denominator,0).
If your formula is IF(big formula=0,0,big formula), and the big formula is numeric (i.e. not text), then you can go IFERROR(big formula^-1^-1,0).