+ Reply to Thread
Results 1 to 13 of 13

Selecting Numbers based on Complex Criteria

  1. #1
    Registered User
    Join Date
    10-28-2010
    Location
    NY, NY
    MS-Off Ver
    Excel 2003
    Posts
    4

    Selecting Numbers based on Complex Criteria

    Sorry for making such confusion. I'm a newbie and needs help to write the formula to arrive at the sum presented in C1. I only know the simplest form like C1 = sum(a1+b1)

    RULE #1
    If A1 = 0, 1, 7, or 8 and B1 = any number, then C1 = 0
    If A1 = 2 and B1 = any number, then C1 =1
    If A1 = 3 and B1 = 0, 1, or 2, then C1 = 2
    If A1 = 4 and B1 = 0, 1, or 2, then C1 = 3
    If A1 = 3 or 4 and B1 = 3, then C1 = 4

    RULE #2
    If A1 = 0, 1, 2, 7, or 8 and B1 = 0, 1, or 8, then C1 = 0
    If A1 = 0, 1, 2, 7, or 8 and B1 = 2 or 3, then C1 = 2
    If A1 = 3 or 4 and B1 = 0 or 1, then C1 = 2
    If A1 = 3 and B1 = 2 or 3 , then C1 = 3
    If A1 = 4 and B1 = 2 or 3, then C1 = 4

    Any number is 0, 1, 2, 3, 8 (no 4, 5, 6, or 7)

    Thanks in advance!
    Last edited by Deks; 10-29-2010 at 03:56 PM. Reason: original title did not meet requirements

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Help Newbie w/ formula!

    Que? I from Barcelona....

  3. #3
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Help Newbie w/ formula!

    if you change your title to perhaps "Selecting Numbers based on Complex Criteria" and read the FAQ, you will get an answer quickly!
    Regards
    Darren

    Update 12-Nov-2010 Still job hunting!

    If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.

    Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!


  4. #4
    Registered User
    Join Date
    10-28-2010
    Location
    NY, NY
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Help Newbie w/ formula!

    Quote Originally Posted by squiggler47 View Post
    if you change your title to perhaps "Selecting Numbers based on Complex Criteria" and read the FAQ, you will get an answer quickly!
    Thanks for the tip Squig!

  5. #5
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Selecting Numbers based on Complex Criteria

    Try this for your first rules :-

    Please Login or Register  to view this content.
    and this for the second!

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-28-2010
    Location
    NY, NY
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Selecting Numbers based on Complex Criteria

    Quote Originally Posted by squiggler47 View Post
    Try this for your first rules :-

    Please Login or Register  to view this content.
    and this for the second!

    Please Login or Register  to view this content.
    I tried them Darren but the answers were incorrect. I may not have explained it clearly (pls. see attached). I do appreciate the effort you put in to create such a complex formula. My head is still spinning trying to analyze it .
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Selecting Numbers based on Complex Criteria

    Seems part of the formula was wrong, here's a sheet which I think matches your table, C1 and C2 are the 2 formulas


    *** I re-added the file, the references in the formula got broken!, I also added a short breakdown of how the formula works!
    Last edited by squiggler47; 10-30-2010 at 03:51 AM. Reason: CHanged file

  8. #8
    Registered User
    Join Date
    10-28-2010
    Location
    NY, NY
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Selecting Numbers based on Complex Criteria

    Woooot Darren, it worked! And, even a hundred thanks for adding the explanation- friggin' awesome. I think I'm gonna like Excel!

    You are the man!

    I owe you a beer and a Brooklyn pizza!
    Last edited by Deks; 10-30-2010 at 04:45 AM.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Selecting Numbers based on Complex Criteria

    Good on squiggler!

    However, would it not be easier long term to just store 2 truth tables and use them accordingly ? (ie basic INDEX/MATCH, SUMIF etc...)

    Please Login or Register  to view this content.
    As you can see you did not stipulate what should happen re: A: 3,4 & B = 8 - for either rule (C1/C2)

  10. #10
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Selecting Numbers based on Complex Criteria

    I considered other ways, but given the fact that it was un defined what needed to happen in the other cases, I figured this gave the most flexibility to display strings for the missing values if needed! I could see the OP changing the goal posts at the last minute, but since the PDF refers to a standard calculation (MDS 3.0 ADL) I guess it is fixed!

    I assume as well that this formula would be fairly fast as most of it is ignored!

  11. #11
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Selecting Numbers based on Complex Criteria

    Quote Originally Posted by squiggler47 View Post
    ...since the PDF refers to a standard calculation (MDS 3.0 ADL) I guess it is fixed

    I assume as well that this formula would be fairly fast as most of it is ignored
    I wasn't suggesting tables on grounds of efficiency but if as you say the values are fixed then the use of lookup tables largely loses its appeal (bar transparency).

  12. #12
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Selecting Numbers based on Complex Criteria

    the other way :-
    Formula 1

    Please Login or Register  to view this content.
    formula 2
    =IF(ISERROR(--MID(CHOOSE(B3+1,"0022xxxx0","0022xxx0","0022xxxx0","2233xxxxx","2244xxxxx","xxxxxxxxx","xxxxxxxxx","0022xxxx0","0022xxxx0"),C3+1,1)),"INVALID",--MID(CHOOSE(B3+1,"0022xxxx0","0022xxx0","0022xxxx0","2233xxxxx","2244xxxxx","xxxxxxxxx","xxxxxxxxx","0022xxxx0","0022xxxx0"),C3+1,1))

    I used the choose statement just to make it easier to change the criteria!

  13. #13
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Selecting Numbers based on Complex Criteria

    Interesting!

    =IF(ISERROR(--MID("0000xxxx00000xxxx01111xxxx12223xxxxx3334xxxxxxxxxxxxxxxxxxxxxxx0000xxxx00000xxxx0",A3*8+C3+1,1)),"INVALID",--MID("0000xxxx00000xxxx01111xxxx12223xxxxx3334xxxxxxxxxxxxxxxxxxxxxxx0000xxxx00000xxxx0",A3*8+C3+1,1))

    is slower than including the choose statement as in the previous post, that seems counter intuitive to me?

+ 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