+ Reply to Thread
Results 1 to 8 of 8

Why doesnt this nested IF function work? It contains multiple criteria

  1. #1
    Registered User
    Join Date
    08-27-2009
    Location
    Milton Keynes
    MS-Off Ver
    Excel 2007
    Posts
    45

    Lightbulb Why doesnt this nested IF function work? It contains multiple criteria

    If anyone could help me with this it would be very much appreciated, I’ve been trying all sorts of combinations with no luck. You will need to look at the picture I've attached to make sense of the below.

    In (C2+) it should display one of the following results - "Listed", "-" meaning not listed, or "Listing Ended". This is worked out from the corresponding dates to the right of "Selling Status" and in (G2) which contains the current date. So when there is nothing entered in (D2) "Selling Status"(C2) should display "-", if a date is entered into (D2) it should then go to "Listed". This part I’ve managed to accomplish, however, when I try add the next part to the equation, it doesn’t seem to like it.

    If "Listing End Date"(E2) is less than the current date(G2), "Selling Status"(C2) should then go to "Listing Ended".

    This is the current formula I have (below) which does actually display "Listing Ended" However it also contains the "Listed" which it should over right.

    Current Formula - =IF((D2=""),"-","Listed"&IF(E2<G2,"Listing Ended",))
    Attached Images Attached Images
    Last edited by RayJay01; 09-06-2009 at 03:24 PM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,601

    Re: Why doesnt this nested IF function work? It contains multiple criteria

    Try to put $ in cell you want to be locked:

    $G$2 (I think it's G2 not G1 that you need to put in formula).

  3. #3
    Registered User
    Join Date
    08-27-2009
    Location
    Milton Keynes
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: Why doesnt this nested IF function work? It contains multiple criteria

    Sorry, but the same thing happens, I get the result - ListedListed Ended, were it should be one or the other. Any more suggestions please?

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,601

    Re: Why doesnt this nested IF function work? It contains multiple criteria

    =IF((D2=""),"-",IF(E2<$G$2,"Listing Ended","Listed"))

    See is first Listing Ended or Listed in formula

    Edit: also, when you get your solution don't forget to make thread [solved]

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Why doesnt this nested IF function work? It contains multiple criteria

    i think you probably need
    =IF(D2="","-",IF(AND(E2<>"",E2<$G$1)," Listing Ended","listed"))
    because if E is blank it will always be less than g1
    Last edited by martindwilson; 09-06-2009 at 11:54 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Registered User
    Join Date
    08-27-2009
    Location
    Milton Keynes
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: Why doesnt this nested IF function work? It contains multiple criteria

    It does display "Listing Ended" now, however, it doesn't change to "Listed" when a date is placed into (D2) It should go from "-" meaning no data, then i enter a date, it should then say "Listed", when i enter the Listing Ending Date if the current date goes over this, (C2) should then display "Listing Ended". Thanks for trying...

  7. #7
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Why doesnt this nested IF function work? It contains multiple criteria

    works fo me!
    d2 and e2 blank c2 returns -
    d2 date e2 blank c2 returns listed
    d2 date e2 date less than g1 c2 returns listed
    d2 date e2 date greater than g1 c2 returns listing ended
    you might want to consider what happens if e2=g1
    =IF(D2="","-",IF(AND(E2<>"",E2<=$G$1)," Listing Ended","listed"))
    perhaps

  8. #8
    Registered User
    Join Date
    08-27-2009
    Location
    Milton Keynes
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: Why doesnt this nested IF function work? It contains multiple criteria

    Thank you, it worked I just had to change it to (G2) Thanks for all your time.

+ 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