How do i nested IF AND OR with unique values and unique message

[AndyJR]

Hi,

Cell E3 display dynamic reference values between 000 to 333 ( e.i 000,001,002,003, etc,etc)
Each reference have their "own" measure values that is displayed on cells
F3,G3,I3,R3,T3
for example :

=IF(AND(OR(E3={"000"}),OR(F3={1,2,3,4}),OR(G3={4,6,10,14}),OR(I3={1,2,4,5}),OR(R3={5,6,7,8}),OR(T3={0,3,5,7})),"IN","OUT")

The formula work fine, at the cell Z3 it display correctly "OUT" (without quote)

but when i nested the exact same formula with Different reference and different values with "&" it display "OUT" "OUT" (twice)

please can someone tell me where is the error? or how i do nest both formula?

PS
in reality there are 24 formulas with different reference and values that i have to nested


Thank you !!

 

[thisoldman]

The "&" is the concatenation operator. "&" is not used for nesting IF formulas.

="OUT"&"OUT"
gives the same results as
=CONCATENATE("OUT","OUT")

There's a minor change you might make to your working formula.

=IF(AND(E3="000",OR(F3={1,2,3,4}),OR(G3={4,6,10,14}),OR(I3={1,2,4,5}),OR(R3={5,6,7,8}),OR(T3={0,3,5,7})),"IN","OUT")

I do suggest that you not nest your IF formulas. Nesting will create a monster formula that will be too complex to understand or test. You should consider using helper columns.

 

[AndyJR]

Hi thisoldman,

It means i can't nested with out helper column? aww..

Thanks !!

 

[AndyJR]

Hi,

I Know i can do like this :

=IF(AND(OR(E3={"000","001","002","003"}),OR(F3={1,2,3,4}),OR(G3={4,6,10,14}),OR(I3={1,2,4,5}),OR(R3={5,6,7,8}),OR(T3={0,3,5,7})),"IN","OUT")

and for sure will not display the message text twice, But each reference are going to share global values , not the unique values that belong to each reference.
see what's my problem?


Thanks

 

[Matty]

Hi,

Might be better to post a simple example of your data as well as detailing what you're trying to do. This will make it easier for potential helpers.

Matty

 

[AndyJR]

Hi Matty,

Thanks for your response, and Yes i'll post an small sample with phony data.

Thanks

 

[AndyJR]

Hi Matty,

I can't download the Mr excel HTML maker, i select the file, click on download and nothing happen.
So i'm copy and paste

NOTE:
References E3:E12 (012,001,010..000) Have unique values across
Because references are dynamic, then i need to nest the formula multiples times for each Reference with their own values, and at the end of the (then) long formula i need to insert the Text "IN", "OUT" , Then drag it from X3 to X12, - NOTE: I understand the issue In the formula E3, it recognize 001 as 1, 000 as 0, the 010 as 10 , etc (the range E3:12 Is formatted as a

[TABLE="width: 438"]
<colgroup><col><col><col><col><col><col><col span="8"><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Col>[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T [/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD="align: left"]

Row

<tbody>
</tbody>

[/TD]
[TD]val[/TD]
[TD]HTt[/TD]
[TD]Sk[/TD]
[TD]-[/TD]
[TD]L3*3[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]9*L[/TD]
[TD]-[/TD]
[TD]Tran[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]REF[/TD]
[TD]TEXT[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]012[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]-[/TD]
[TD]6[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]0[/TD]
[TD]-[/TD]
[TD]0[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]012[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]001[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]-[/TD]
[TD]1[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]4[/TD]
[TD]-[/TD]
[TD]19[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]001[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]010[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]-[/TD]
[TD]7[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]8[/TD]
[TD]-[/TD]
[TD]21[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]010[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]200[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]-[/TD]
[TD]8[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]5[/TD]
[TD]-[/TD]
[TD]4[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]200[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]201[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]-[/TD]
[TD]9[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]4[/TD]
[TD]-[/TD]
[TD]2[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]201[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]101[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]-[/TD]
[TD]2[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]2[/TD]
[TD]-[/TD]
[TD]6[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]101[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]020[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]-[/TD]
[TD]0[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]3[/TD]
[TD]-[/TD]
[TD]5[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]020[/TD]
[TD]OUT[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]000[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]-[/TD]
[TD]4[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]8[/TD]
[TD]-[/TD]
[TD]7[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]000[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]010[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]-[/TD]
[TD]3[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]1[/TD]
[TD]-[/TD]
[TD]21[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]010[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]000[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]-[/TD]
[TD]5[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]8[/TD]
[TD]-[/TD]
[TD]7[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]000[/TD]
[TD]OUT[/TD]
[/TR]
</tbody>[/TABLE]

 

[Matty]

Hi Andy,

Thanks for the data.

What rules must be observed to make a reference be "In" versus making it "Out"?

Matty

 

[AndyJR]

Hi Matty,

the Columns E, F ,G,I ,R,T are dynamic, so when E3 Change, F,G,I,R,T pull some data that is not exact at all time
rules EXAMPLE:
E3 = 012, a reference
F3=1, G3=5 ,I3=6, R3=0, T3=0 <- each column of Row 3 have actual numbers (measure) (in blue to identify)
Then If Formula have 1 numbers different is OUT Otherwise IN

E4 = 001, a reference
F4=1, G4=2 ,I4=2, R4=4, T4=19 <-column of Row 4 have actual numbers
Then If Formula have 1 numbers different is OUT Otherwise IN

E5 = 010, As a reference
F5=2, G5=1 ,I5=7, R5=8, T5=21 <- each column of Row 5 actual numbers
Then If Formula have 1 numbers different is OUT Otherwise IN

etc, etc,etc....

The problem is that because E3:E12 (REFERENCE) are dynamic, Then
each reference can be displayed in any row, e.i
012 is displayed in E3 now, But tomorrow it might be on Row E10 (with it own values which it change time to time)

So that's why i need to nest 24 reference with their values

Hope this explanation is good., if not please let me know..

Thanks!

 

[Matty]

Hi,

Sounds like a better option would be to maintain a separate table with all of the possible combinations for each reference (as opposed to trying to capture all of the rules within a formula).

You could then use a simple lookup formula to check the validity of the numbers shown across columns F, G, I, R and T.

Does this make sense?

Matty