+ Reply to Thread
Results 1 to 7 of 7

Nested IF Function

  1. #1
    Registered User
    Join Date
    07-01-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    66

    Nested IF Function

    Hi Champs,

    See below formula that i have updated in a cell.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If I updated any other character like an Alphabet or Symbol in L6, it still gives me one of the true values in the formula.
    What i am expecting is that, if a user updates an Alphabet or Symbol in L6, the formula should populate "ERROR"

    Can you please help.

    Aaron

  2. #2
    Registered User
    Join Date
    08-04-2009
    Location
    Golborne England
    MS-Off Ver
    2013
    Posts
    85

    Re: Nested IF Function

    try this.

    Please Login or Register  to view this content.
    JimBobBowie

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

    Re: Nested IF Function

    Try

    =IFERROR(CHOOSE(SIGN(L6)+2,"Debit NLPX-VAA - Credit NLP MAIN","No wire","Debit NLP MAIN - Credit NLPX-VAA",2,3),"Error")

  4. #4
    Registered User
    Join Date
    07-01-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Nested IF Function

    Hi Jonmo1 - Your formula worked like a charm and all the if conditions are met. Would you please help me understand the formula though, specially this part
    Please Login or Register  to view this content.
    . Does it take the numbers as a sequence?

    Hi JimBobBowie - Your formula worked for an alphabet. But if the cell is blank or 0, it would still show as Error instead of showing as No Wire

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

    Re: Nested IF Function

    The SIGN function returns the SIGN of a given number.
    If the number is Negative (<0) it returns -1
    If the number is 0, it returns 0
    If the number is Positive (>0) it returns 1
    If it's not a number at all, it returns #Value! (that's where IFERROR comes in)

    The +2 changes the result of SIGN so it returns 1 2 or 3 instead of -1 0 or 1

    Then CHOOSE Chooses an option based on the number provided
    CHOOSE(number,Option1,Option2,Option3,etc..)

  6. #6
    Registered User
    Join Date
    07-01-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Nested IF Function

    I pretty much tried just the Sign and Choose function independently to understand it further. Thanks for your help.

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

    Re: Nested IF Function

    You're welcome.

+ 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. COUNTIF/COUNTIFS function + nested user-defined function
    By shamjamali in forum Excel General
    Replies: 1
    Last Post: 05-12-2015, 09:12 PM
  2. [SOLVED] Nested Function With Division - Receiving #VALUE! Function Error
    By DDM64 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-17-2013, 01:16 PM
  3. Replies: 6
    Last Post: 12-14-2012, 10:43 PM
  4. nested MATCH function within the INDEX function
    By thechoosenonesdream in forum Excel General
    Replies: 4
    Last Post: 10-25-2012, 03:55 PM
  5. [SOLVED] Nested AND function within IF function is only addressing logical1 but not logical2
    By betic in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-20-2012, 09:17 AM
  6. Replies: 9
    Last Post: 07-02-2012, 07:02 PM
  7. 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1