+ Reply to Thread
Results 1 to 8 of 8

Excel 2007 : Nested IF formulas

Hybrid View

  1. #1
    Registered User
    Join Date
    07-20-2011
    Location
    Dallas/TX, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Nested IF formulas

    I have a spreadsheet with tons of conditional formatting underlying in several cells. I don't know if it is possible what I want to do, which is

    either of the following IF formulas apply.

    IF(N557="pending","X","R/P")

    (IF(N557="expected","X","X") ... but the word "expected" is followed by a date/word, i.e. the following items vary, the only constant is the word "expected"

    I'm wondering, how to put both IF's in the cell, i.e. either applies, but how to write that cell N557 "contains" the word EXPECTED?
    FYI: I cannot use date only, because the cell will ultimately have a date, so the conditional formatting won't work using a date only instead of putting the word "expected" in front of it.

    Maybe I'm throwing conditional formatting and formulas together, which may not work. But I appreciate any ideas!
    Thanks so much in advance.
    Last edited by SEHRET; 01-25-2012 at 12:30 PM.

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Nested IF formulas

    The FIND function finds text within a cell, and returns an error if it's not found, so:

    =IF(ISERROR(FIND("expected",N557)),"Value if not found","Value if found")

  3. #3
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Nested IF formulas

    Hi Sehret.. Welcome to the forum
    You can try this as well
    =IF(LEFT(N557,8)="Expected","X","X")
    Have fun

  4. #4
    Registered User
    Join Date
    07-20-2011
    Location
    Dallas/TX, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Nested IF formulas

    Thanks for your fast responses, but both won't work on my scenario - or I'm just not seeing it. What I need to do is to get both IF formulas in the same cell in column "S". Currently I only have one formula in the column, but need both in the same cell. Basically a double IF, i.e. if one IF doesn't apply, then apply the other IF. I have no clue how to write it. I've attached a simple screen print.

    Basically column "R" right now has =IF(N609="pending","X","R/P"). "X" is just a parameter and conditionally formatted in white, so that the cell R609 appears to be empty, if N609 is empty. "R/P" stands for "Received/Processing", i.e. R/P appears as formatted in orange, when N609 contains something other than the word "pending", i.e. a date in this case.

    But I also want to add a formula, that if N609 contains the word "expected", it will show "X" for in column "R" for TRUE and FALSE value, so it again can be formatted as white, i.e. not visually showing.

    I just don't know how to get both IF's together into one formula. Maybe I'm not seeing the bigger picture. My intend was to avoid to add another column for the "expected" date to a spreadsheet that's getting big. Column "R" has about 6 different conditional formattings underlying for several other, more simple criteria.

    IF formulas.jpg
    Attached Images Attached Images
    Last edited by SEHRET; 01-24-2012 at 12:01 PM. Reason: spelling correction

  5. #5
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Nested IF formulas

    Have a look at this & see if it's what you're after
    =IF(N557="","",IF(LEFT(N557,7)="Pending","X",IF(LEFT(N557,8)="Expected","X","R/P")))
    If N557 is empty, R557 is blank, If N557 has Expected & anything else if you want, such as a date etc, it returns X, If N557 has Pending & anything else, it returns X, If N557 has anything else in it, it returns R/P. I've attached a sample file for you. Scroll down to row 557 to try it
    Cheers
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-20-2011
    Location
    Dallas/TX, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Nested IF formulas

    YES YES YES!!!! THANK YOU SO MUCH! You made my day! That's it!
    ~Greetings, Sylvia

  7. #7
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Nested IF formulas

    Excellent.. Glad I could help.
    If you're happy with it please mark the thread as Solved
    Cheers

  8. #8
    Registered User
    Join Date
    07-20-2011
    Location
    Dallas/TX, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Nested IF formulas

    Thanks! Already marked as solved yesterday. Will only show in the main threat content as such.
    Thanks again! I really appreciated it!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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