Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2008
    Posts
    150

    Unanswered: Modify Formula to Remove "0" from Empty Cell

    Hello,

    I would appreciate some help with this Excel formula. I use the formula
    =IF(K4=0,0,K5/K4) to remove the presence of 0 when a cell is empty.

    I would like to modify the same formula to this "Paste Link" function ='NTD Raw Data'!L6 so that I can remove the numbers 0 in the empty cells.

    Any help would be appreciated.

    Thank you. :-)

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Not an MSAccess question but you "copy" the "formula" in the excel cell and then "paste" the "formula" in the cells you want to apply it. Excel will take care of knowing what the new row/column is that you're pulling the data from when you paste (just the formula).
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Sep 2008
    Posts
    150
    OMG! I posted in the wrong forum. I apologize for my error.

    I am actually trying to modify the formula “='NTD Raw Data'!L6” to remove the zeros in the cells that do not contain data. This formula "=IF(L10="","",L11/L10)" will give me a blank cell (with no zeros), but I am having problems trying to update the Paste Link formula (='NTD Raw Data'!L6).

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Edit: Quips about the forum accuracy removed since I was beaten to the punchline

    You are aware of a sheetwide option that hides all zeros... in Tools Options... yes?

    Are you also aware that you can use custom formatting in Format Cells to hide zeros?

    Would either of these options help you?

    HTH
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Good call ST.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Sep 2008
    Posts
    150
    Thank you StarTrekker. The sheetwide option did not work, but the format cells option worked.

    Thanks again to both of you for responding. :-)

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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