Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula to Replace Blank with Zero (2002)

    Is there a formula to replace blanks with zeros? For instance, I have a row of data that uses the XNPV function to calculate. When it encounters a blank, it screws up the calculation. Consequently, I would like to replace all blanks w/ zeros?

    Thanks!!!!!!!!!!!!!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula to Replace Blank with Zero (2002)

    Say you have a value in A1 which may or may not be blank.
    You can use <code>IF(A1="",0,A1)</code> within a formula to replace a blank with 0.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula to Replace Blank with Zero (2002)

    Perfect!!!!!!!!!!!!!!!!!!!!!!!!!

    Thanks.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts

    Re: Formula to Replace Blank with Zero (2002)

    I prefer using
    N(A1)
    instead of IF(A1="",0,A1)

    It turns any non-numeric value (including blanks, spaces, nulls, any text) into a zero - while leaving numbers as thay are.

    zeddy

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula to Replace Blank with Zero (2002)

    That has the advantage of being considerably shorter, but in some situations it could be a disadvantage that text values are converted to 0 - this could cause formulas to return an unexpected value instead of an error.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts

    Re: Formula to Replace Blank with Zero (2002)

    You are of course correct.
    It always depends on the situation.
    But I've seen so many spreadsheets where Users 'blank' out data using the spacebar..

    zeddy

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula to Replace Blank with Zero (2002)

    N can certainly be useful, no doubt about that!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •