How can we improve Excel for Windows (Desktop Application)?

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

66 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)

    We’ll send you updates on this idea

    figbootfigboot shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    under review  ·  Excel Team [MSFT]AdminExcel Team [MSFT] (Admin, Office.com) responded  · 

    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.

    Thanks
    John [MS XL]

    6 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Michael BishopMichael Bishop commented  ·   ·  Flag as inappropriate

        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.

      • Anonymous commented  ·   ·  Flag as inappropriate

        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?

      • PaulPaul commented  ·   ·  Flag as inappropriate

        =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.

      • Anonymous commented  ·   ·  Flag as inappropriate

        Just a general IFCONDITION() function would be fine, whether it be blank, zero, 42, whatever.

      • KeithKeith commented  ·   ·  Flag as inappropriate

        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 BarberKenneth Barber commented  ·   ·  Flag as inappropriate

        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).

      Feedback and Knowledge Base

      How it works

      • Create a user account if you want to add a new suggestion or vote on existing suggestions.
      • Select one of the feedback forums listed.
      • Check out the ideas others have suggested and vote on your favorites.
      • Be sure to search for your suggestion but if you can’t find something similar enough, you can submit your own.
      • Keep suggestions focused on a single idea per post and limited to 25 words or less.
      • When you post an idea to our forum, others will be able to subscribe to it and make comments.