# Selecting Numbers based on Complex Criteria

1. ## 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)

2. ## Re: Help Newbie w/ formula!

Que? I from Barcelona....

3. ## 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!

4. ## Re: Help Newbie w/ formula!

Originally Posted by squiggler47
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. ## 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. ## Re: Selecting Numbers based on Complex Criteria

Originally Posted by squiggler47
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 .

7. ## 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!

8. ## 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!

9. ## 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. ## 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. ## Re: Selecting Numbers based on Complex Criteria

Originally Posted by squiggler47
...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. ## 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. ## 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?

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

#### 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