+ Reply to Thread
Results 1 to 10 of 10

Formula returns a zero value instead of expected empty value

  1. #1
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    313

    Formula returns a zero value instead of expected empty value

    In the following code (in column V) when U18 is equal "" I get a value of zero when I expected a "" null value. This causes a problem in sorting.

    [code] =IF($U18="",$H18,"X"){/CODE]

    Both col U and H are defined as "General", however I've tried changing both to "Text" with same result.

    I appreciate any suggestions.

    Thanks

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Formula returns a zero value instead of expected empty value

    Well, what's in H18 ?

    If U18 is "" , then the formula returns the contents of H18

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Formula returns a zero value instead of expected empty value

    What is the formula in H18 that is resulting in ""? Your formula should work and bring over the null value. If you are simply formatting H18 as text, then Excel will still see that as a blank cell and return 0.
    Last edited by ChemistB; 07-25-2017 at 10:09 AM.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Formula returns a zero value instead of expected empty value

    Try:

    =IF($U18="",IF($H18="","",$H18),"X")

  5. #5
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    313

    Re: Formula returns a zero value instead of expected empty value

    H18 has value "" (null)

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Formula returns a zero value instead of expected empty value

    If H18 is 'empty', not even a formula, then as ChemistB said, it will be considered 0.
    So you'll need to test both cells for being blank, like Phuocam shows in post #4.

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Formula returns a zero value instead of expected empty value

    See my example below. If your formula is returning 0, then H18 does not equal ""
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    313

    Re: Formula returns a zero value instead of expected empty value

    H18 and U18 are formatted as "General" and H18 is populated by a macro:

    Please Login or Register  to view this content.

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Formula returns a zero value instead of expected empty value

    Your code is not populating H with "", it is using the value in H = "" as a boolean expression.

    Unfortunately, in determining True/False, Excel will give a TRUE if H is empty or has "" for the expression H="". It doesn't differentiate. :/
    The reverse is not true (ISBLANK or =0 does differentiate)
    See tautology chart added to my file.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    07-20-2015
    Location
    Jacksonville, Florida
    MS-Off Ver
    Excel 2016
    Posts
    313

    Re: Formula returns a zero value instead of expected empty value

    Thank you for the example - that worked and solved the issue,

    Thanks to all of the suggestions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Close up empty rows after IF formula returns 'true' results
    By Cupsay in forum Excel General
    Replies: 12
    Last Post: 03-04-2016, 11:32 PM
  2. Pivot table picks up empty cell from formula which returns BLANK
    By donyc in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-11-2013, 08:37 PM
  3. [SOLVED] need formula that finds phone #, if true, returns empty cell
    By jessexcel in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-23-2013, 11:44 PM
  4. VBA - Sensitivity analysis (Expected returns)
    By HenFors in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-25-2013, 01:27 PM
  5. Macro returns expected result
    By cossie2k in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-09-2012, 08:25 AM
  6. Replies: 5
    Last Post: 02-10-2009, 03:26 AM
  7. Formula returns #Value! empty cell value
    By ingineu in forum Excel General
    Replies: 4
    Last Post: 10-02-2006, 05:49 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1