Selecting Numbers based on Complex Criteria

[Deks]

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!

[Marcol]

Que? I from Barcelona....

[squiggler47]

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

[Deks]

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!

[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.

[Deks]

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 .

[squiggler47]

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!

[Deks]

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!

[DonkeyOte]

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)

[squiggler47]

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!

[DonkeyOte]

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

[squiggler47]

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!

[squiggler47]

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?