+ Reply to Thread
Results 1 to 4 of 4

How to use ISNONTEXT in nested IF statement

  1. #1
    Registered User
    Join Date
    10-18-2016
    Location
    Washington, DC
    MS-Off Ver
    Excel 2013
    Posts
    6

    How to use ISNONTEXT in nested IF statement

    Hi!

    So I have a table that reads the following for Allotments and Obligations and what is DESIRED for Remainder:


    Allotments | Obligations | Remainder

    $15 | $10 | $5

    $15 | *blank but formula in cell* |$15

    The Obligations column serves as both an input (whole number) and receives data from an iterative circular reference that I have created. I would like for the Remainder column to take the difference between Allotments and Obligations if the entries are nonzero and would like for the column to produce the allotment amount of the obligations column is "blank" (a formula exists here). The v

    I have written a nested IF statement with an ISNONTEXT function but Excel will not accept it. Any suggestions would be greatly appreciated!

    =IF(AND(A1<>0,B1<>0),A1-B1,IF(AND(A1<>0,ISNONTEXT(B1)="FALSE"),A1,""))

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,463

    Re: How to use ISNONTEXT in nested IF statement

    You should remove the quotes from around the FALSE value in your formula, like this:

    =IF(AND(A1<>0,B1<>0),A1-B1,IF(AND(A1<>0,ISNONTEXT(B1)=FALSE),A1,""))

    Hope this helps.

    Pete

  3. #3
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,414

    Re: How to use ISNONTEXT in nested IF statement

    Hello,

    Maybe something like this :

    Please Login or Register  to view this content.
    Note that 'FALSE' must not be between quotes otherwise, you compare to the string "FALSE" and not the boolean value FALSE
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2024
    Posts
    6,204

    Re: How to use ISNONTEXT in nested IF statement

    Try this ...

    =IF(AND(A1<>0,ISNONTEXT(B1)=FALSE),A1,IF(AND(A1<>0,B1<>0),A1-B1,""))

    Or:

    =A1-N(B1)

+ 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. Nested IF statement error. Nested True statement is not triggering
    By Lucas7040 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-26-2016, 11:41 AM
  2. Replies: 2
    Last Post: 07-09-2015, 04:25 PM
  3. [SOLVED] If statement to select data - nested statement - assistance
    By petitesouris in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-29-2015, 09:55 PM
  4. need to combine SUBSTITUE and IF(ISNONTEXT) formulas
    By CharlieRuba in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-25-2014, 12:04 PM
  5. Replies: 6
    Last Post: 01-14-2009, 06:59 PM
  6. What're differences between ISTEXT ISNONTEXT ISNUMBER?
    By nattasiray in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-07-2007, 01:25 PM
  7. =IF(ISNONTEXT(Sheet1!C2),(A5=A5+1),(A5=1))
    By famdamly in forum Excel General
    Replies: 1
    Last Post: 12-11-2005, 11:15 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1