+ Reply to Thread
Results 1 to 15 of 15

ISBLANK function nested in IF Function

  1. 05-27-2016, 10:03 AM #1
    erswan
    erswan is offline
    Registered User
    Join Date
    05-27-2016
    Location
    Columbus, IN
    MS-Off Ver
    MS Office 2013
    Posts
    4

    ISBLANK function nested in IF Function

    Hi,

    I am trying to create a table that automatically populates a new column with data at the beginning of every year. The data would be extracted from a separate table, labeled Assumptions. If the year has not come to pass yet, then the column would remain blank. For example, if A1 = 2013, then A2 would extract the value from 'Assumptions!L49'. If A1 is currently blank, then no value should be returned in A2. So far, this is the function that I have came up with:

    A2=IF(ISBLANK(A1),Assumptions!L49,"")

    Currently, it appears as if the function is working, but the logic seems backwards to me. If A1 is blank, then the true part of the IF function should be " ", and not 'Assumptions!L49, correct?

    Please let me know if I'm wrong. Thank you.
    Reply With QuoteRegister To Reply
  2. 05-27-2016, 10:07 AM #2
    mikeTRON
    mikeTRON is offline
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: ISBLANK function nested in IF Function

    Yeah it seems you are backwards.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
    Reply With QuoteRegister To Reply
  3. 05-27-2016, 10:19 AM #3
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,064

    Re: ISBLANK function nested in IF Function

    "If A1 is currently blank, then no value should be returned in A2"

    So shouldn't that be
    in A2
    =IF(ISBLANK(A1),"",Assumptions!L49)
    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.
    Reply With QuoteRegister To Reply
  4. 05-27-2016, 10:27 AM #4
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: ISBLANK function nested in IF Function

    Or try this

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  5. 05-27-2016, 10:43 AM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: ISBLANK function nested in IF Function

    Like Alkey, I prefer to use "" rather than ISBLANK()...

    A2=IF(A1="","",Assumptions!L49)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  6. 05-27-2016, 10:50 AM #6
    erswan
    erswan is offline
    Registered User
    Join Date
    05-27-2016
    Location
    Columbus, IN
    MS-Off Ver
    MS Office 2013
    Posts
    4

    Re: ISBLANK function nested in IF Function

    Hi, I tried what you said and it works but it keeps returning a 0, instead of a blank cell?
    Reply With QuoteRegister To Reply
  7. 05-27-2016, 10:59 AM #7
    erswan
    erswan is offline
    Registered User
    Join Date
    05-27-2016
    Location
    Columbus, IN
    MS-Off Ver
    MS Office 2013
    Posts
    4

    Re: ISBLANK function nested in IF Function

    I've done a bit of testing and it appears as though my IF functions are set to work the other way around? Is this normal? haha
    Reply With QuoteRegister To Reply
  8. 05-27-2016, 11:37 AM #8
    nimrod
    nimrod is offline
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: ISBLANK function nested in IF Function

    A2=IF(ISBLANK(A1),Assumptions!L49,"")

    Function Structure : IF(logical_test,Value_if_true, Value_if_false)

    In your case when A1 is blank the formula will show the value in L49 ... in short you have put you values backwards.
    Reply With QuoteRegister To Reply
  9. 05-27-2016, 11:38 AM #9
    nimrod
    nimrod is offline
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: ISBLANK function nested in IF Function

    A2=IF(ISBLANK(A1),Assumptions!L49,"")

    Function Structure : IF(logical_test,Value_if_true, Value_if_false)

    In your case when A1 is blank the formula will show the value in L49 ... in short you have put you values backwards.
    Reply With QuoteRegister To Reply
  10. 05-27-2016, 11:45 AM #10
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: ISBLANK function nested in IF Function

    Quote Originally Posted by erswan View Post
    I've done a bit of testing and it appears as though my IF functions are set to work the other way around? Is this normal? haha
    If statements can work either way. The "test" is what determines which way round they need to be...
    A2=IF(A1="","",Assumptions!L49)
    or
    A2=IF(A1<>"",Assumptions!L49, "")
    do the exact same thing. It is often a matter of preference, I prefer to test if the cell is empty, other prefer to test if the cell is NOT empty
    Reply With QuoteRegister To Reply
  11. 05-27-2016, 12:05 PM #11
    nimrod
    nimrod is offline
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: ISBLANK function nested in IF Function

    IsBlank NOT Synonymous with testing for =""

    It should also be noted that testing for "" does not always return the same value as isBlank.

    For example if a cell A2 has the formula A2=IF(A1<>"",Assumptions!L49, "") and when the value returned from formula in cell is "" then isBlank(a2) = False while A2="" is True
    In short both tests return True when cell is empty but disagree when cell has an empty string.
    Reply With QuoteRegister To Reply
  12. 05-27-2016, 12:15 PM #12
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: ISBLANK function nested in IF Function

    Quote Originally Posted by nimrod View Post
    IsBlank NOT Synonymous with testing for =""

    It should also be noted that testing for "" does not always return the same value as isBlank.
    true, it depends on if the cell is empty or not

    For example if a cell A2 has the formula A2=IF(A1<>"",Assumptions!L49, "") and when the value returned from formula in cell is "" then isBlank(a2) = False while A2="" is True
    In short both tests return True when cell is empty but disagree when cell has an empty string.
    A
    B
    C
    19
    		 isblank ""
    20
    TRUE
    TRUE
    21
    FALSE
    TRUE
    22
    0
    FALSE
    FALSE

    A20 = empty (contains nothing at all)
    A21=IF(A20="","",A20)
    A22=A20

    B20=ISBLANK(A20) copied down
    C20=A20="" copied down

    ISBLANK will test if the cell is empty, it will return FALSE if the cell contains anything at all, even a null value from a formula
    ="" will return TRUE if the cell is empty OR if the cell contains a null value from a formula

    I hardly ever use ISBLANK, in most cases it is an unnecessary function.
    ="" does the same thing plus more. You can use it to test if a cell is truly empty, such as a date has not been entered into a list yet, or to test a formula to see if it is returning a null string
    Reply With QuoteRegister To Reply
  13. 05-27-2016, 12:25 PM #13
    nimrod
    nimrod is offline
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: ISBLANK function nested in IF Function

    There are other cases as well ... for example utilizing the two tests in a compound formula returns very different results
    e.g. =ISBLANK(TRIM(A1)) will always return false while =TRIM(A1)="" returns true for empty cell, for cell with spaces and empty strings
    Reply With QuoteRegister To Reply
  14. 05-27-2016, 12:26 PM #14
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: ISBLANK function nested in IF Function

    .....ok....
    Reply With QuoteRegister To Reply
  15. 05-27-2016, 01:29 PM #15
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: ISBLANK function nested in IF Function

    erswan, happy to help and thanks for the feedback
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calling function inside function. (aka nested function)
    By jakopak in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-02-2015, 05:58 AM
  2. COUNTIF/COUNTIFS function + nested user-defined function
    By shamjamali in forum Excel General
    Replies: 1
    Last Post: 05-12-2015, 09:12 PM
  3. [SOLVED] I think I need a nested If function and maybe an "ISBLANK" function
    By GWells in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-06-2013, 08:25 AM
  4. Help with ISBLANK function referring to a cell with another ISBLANK formula
    By camdameron in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-23-2011, 12:45 PM
  5. Function like ISBLANK, but not exactly
    By dongna in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-26-2008, 04:57 AM
  6. [SOLVED] isblank() function
    By George in forum Excel General
    Replies: 6
    Last Post: 07-14-2006, 07:15 AM
  7. [SOLVED] Offset function with nested match function not finding host ss.
    By MKunert in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-21-2006, 06:50 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1