+ Reply to Thread
Results 1 to 8 of 8

IF statement - leave cell blank when false

  1. #1
    Registered User
    Join Date
    07-06-2018
    Location
    Kiev,Ukraine
    MS-Off Ver
    2016
    Posts
    8

    IF statement - leave cell blank when false

    I am using "" for the value_if_false in a formula, and as expected the cell appears empty when the logical test is false. However, in the case of the cell to the left of being true, the text in the that cell is truncated. It appears as if Excel is reading the false cell to have something in it. Any suggestions?

    IF(logical_test,value_if_true,value_if_false)
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: IF statement - leave cell blank when false

    The cell is not truncated, the width of the columns isnt wide enough to diaplsy Washington or Cambodia.

    If you use more than 6 IFs in a formula you should be looking at setting up a table.
    That formula can be shortened drastically.

    Thinking, though this may take some time...
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: IF statement - leave cell blank when false

    Oh you have hidden columns with a lot more trips!
    That may complicate things.

  4. #4
    Registered User
    Join Date
    07-06-2018
    Location
    Kiev,Ukraine
    MS-Off Ver
    2016
    Posts
    8

    Re: IF statement - leave cell blank when false

    I didn't mean truncated, wrong word sorry! But usually Excel will allow text to spillover into the next column unless there is something in the column.

    As far as the long IF statement, I agree it is not ideal. I tried some other options but this is the only one that would work on the end users's version of Excel

  5. #5
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: IF statement - leave cell blank when false

    The truncation/result is not fully visible.
    Yep just did a quick test.

    If a cell is completely blank (has no value OR formula) the text will spill over into the next cell but as soon as you put a value or formula in there it doesnt do that.
    e.g.

    open a blank sheet
    in A1 put 1
    in A2 put IF(A1=1,"Washington","") result is "Washington" clearly displayed spilling over into B2.
    But if you copy the formula from A2 to B2 even though the result in B2 is blank (formula has changed to IF(B1=1... ) "Washington" is no longer fully displayed

  6. #6
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: IF statement - leave cell blank when false

    Might be worth looking at the Original data sheet rather than the Calendar for Trip data.

  7. #7
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: IF statement - leave cell blank when false

    Here you go, this works (and I'm very proud of this though the solution is not mine, it's a common solution when using multiple conditions to return a value)

    in CD4
    =IFERROR(LOOKUP(2,1/('Original Data'!$C$2:$C$500=$A4)/(CD$3>='Original Data'!$H$2:$H$500)/(CD$3<='Original Data'!$I$2:$I$500),('Original Data'!$E$2:$E$500)),"")
    and copy across and down as far as EJ13

    That removes all those nasty IFs

    Explanation:

    This is basically

    IFERROR(LOOKUP(2,1/(set of conditions),(result if all condtions are true),"")

    ('Original Data'!$C$2:$C$500=$A4)/(CD$3>='Original Data'!$H$2:$H$500)/(CD$3<='Original Data'!$I$2:$I$500)
    These are 3 condtions (does the advisor match the Original Data and is the CD3 date within his trip ranges). The "/" should be regarded as AND. The result will either be TRUE (1) or FALSE (0) for each condition and the overall result will be TRUE (1) or FALSE(0).

    1 / FALSE (0) will cause an error, this occurs when the CD3 date for an advisor is outside all of that advisor's date ranges. in this case a blank is returned.

    However, if all those conditions are TRUE (1) the result will be 1.
    1/ 1 is 1. But as the LOOKUP is searching for 2, it goes past the 1 / 1 and returns the second part of the formula ('Original Data'!$E$2:$E$500) resulting in the destination when the CD3 date is within a trip range for the given advisor.

    NOTE: This won't work if a CD3 date occurs in more than one date range for a given advisor. It'll just return the first one that matches. Luckily this is not the case with your data at present.
    Last edited by Special-K; 07-10-2018 at 06:05 AM.

  8. #8
    Registered User
    Join Date
    07-06-2018
    Location
    Kiev,Ukraine
    MS-Off Ver
    2016
    Posts
    8

    Re: IF statement - leave cell blank when false

    Thank you! That is a much simpler formula and I can delete all those hidden columns. It still displays only the first few letters of each
    trip location but I think we are just going to have to live with that. I learned a lot from this exercise and from your suggestions. Thank you!

+ 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. How to Leave Truly Empty Cell if Criteria for IF statement is FALSE
    By lowprofile in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-02-2014, 01:24 PM
  2. Summing Cells that contain an IF Statement to leave the cell blank if zero
    By sweeteri in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-14-2014, 12:58 PM
  3. IF Statement to leave cell blank if multiple cells are all blank
    By sweeteri in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-14-2014, 12:02 PM
  4. [SOLVED] Instead of having FALSE or #N/A leave a cell blank
    By Mr_Nick666 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-11-2013, 06:28 AM
  5. If Then formula. If false, I would like to leave the cell blank.
    By joaquinwalking in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-30-2013, 01:56 PM
  6. IF Statement to leave cell completely blank
    By david1987 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-28-2012, 10:27 AM
  7. False - want to leave blank
    By redlion3 in forum Excel General
    Replies: 1
    Last Post: 11-26-2007, 06:01 PM

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