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

102 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

    figboot shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    under review  ·  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]

    8 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...
      • miko commented  ·   ·  Flag as inappropriate

        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.
        Fingers crossed!

      • Michael Bishop commented  ·   ·  Flag as inappropriate

        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  ·   ·  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?

      • Paul 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.

      • Keith 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 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