number analysis

[utmw2016]

Hello ,
I 'd like to find the row inwhich 9 is in anywhere (column A,B,C AND D) and
the rest 3 column, 2cell combination till one number( 3+8=11=2) is equal to the other one.

in example , row 7 and row 14 should return true.
is it possible to use formula?
if not, how possible steps we can use formula to nearest result?
Thank you .

[FlameRetired]

Try this formula.

Please Login or Register  to view this content.

It returns TRUE for rows 7, 12 and 14.

[FlameRetired]

Of course it would be much simpler with helper columns E to H.

Row\Col	A	B	C	D	E	F	G	H	I	J
1	4	1	2	5						In E1:H17 this :=IF(COUNTIF($A1:$D1,9),SMALL($A1:$D1,COLUMNS($A:A)),"")
2	2	2	3	5						In I1:I17 this :=IF(SUM($E1:$H1),IF(OR(SUM(E1:G1)=11,SUM(E1:F1)=11,SUM(F1:G1)=11,SUM(E1,G1)=11),TRUE,""),"")
3	6	3	2	3
4	6	2	0	9	0	2	6	9
5	2	6	5	5
6	5	2	6	3
7	1	5	5	9	1	5	5	9	TRUE
8	2	2	4	4
9	3	4	8	2
10	6	7	4	8
11	8	6	7	1
12	6	3	5	9	3	5	6	9	TRUE
13	5	1	1	2
14	9	2	3	8	2	3	8	9	TRUE
15	5	5	0	7
16	6	5	5	7
17	4	5	6	5

[utmw2016]

Hello FlameRetired,
Thank you very much for your complete solution.
I am new learner and I don't know how to think possible way to solve the problem.
Your 2 step solution is more clear for me to see the way you settle. I really love it.

Only one thing, because of my poor english explaination, your formula is design for working 2 number combination of 11.
so,row 12 return value as true.
Actually I dont want to count this row 12 as true and
I don't mean to count only 11 of 2 number combination fixed.
let me say if another row add as 7,8,8,9, also should be result true. because 8+8=16=7.

What I am looking for is
let say in row 7, except 9, the rest are 1,5 and 5.
1+5=6 match to third number 5- not equal
5+5=10=1 match to third number 1- yes ,equal
so, row 7, is true.

in row 12,
exclude 9,
remain 3 numbers are 3,5 and 6.
3+5<>6
3+6<>5
5+6<>3
so, I don't want it as true.

i am not sure to say that i should use OR((E1+F1)=G1,(E1+G1)=F1,(F1+G1)=E1)) in your formula but how to do if 2 number combine is not single number, like 11,12,13,14 etc.
Hope you will understand what i say.

Anyway I thank you for what you teach me.
all the best
tin

[FlameRetired]

That's clearer and a much different question than I understood initially.

Am going to have to spend some time with this one. It is unusually challenging. I love those kind.

I will work on this as time permits. Perhaps others will provide solution(s) in the meantime. This may require VBA. If you are not familiar with VBA it is programming language that enables the user to define macros as well as their own functions. Perhaps someone will propose such a User Defined Function (UDF) for you.

[FlameRetired]

Re-edited

I've had some additional thoughts.

1. Will there ever be any 2 digit numbers to consider? Your example shows only single digits.
2. Will the 9s ever be the 2nd or 3rd number?
3. In the case of row 7 where the remaining numbers are 1, 5 , 5 and in that order suppose instead the order is 5, 1, 5 :

5+1=6<>the remaining 5
1+5=6<>the other remaining 5
But
5+5=10=1 : in this case would this also be TRUE? Or are we to consider only the sum of adjacent numbers?

[utmw2016]

Hi FlameRetired,
Thanks for your interest and response.
1. I am just try to analysis 4 digit lottery number .
I did 4d diverse to 4 single numbers.
2. 9 is anywhere in position.
3. not meant for adjacent numbers only.

but as you made 2 step solution, i try to put another helping column (A&B,A&C,b&C) and put the formula combination =A1-FLOOR(A1-1,9) to match with the A1,B1&C1.
But fail to do also.

Let me say I am trying to see the same form of number eg.2119, 8449, 4769,2395,8796 etc.
number 9 is constant there,2number combinatin is equal to the third number, not counting where ever their position.

May be it's funny to think,
But I still want to.
regds,

[utmw2016]

Of course it would be much simpler with helper columns E to H.

Row\Col	A	B	C	D	E	F	G	H	I	J
1	4	1	2	5						In E1:H17 this :=IF(COUNTIF($A1:$D1,9),SMALL($A1:$D1,COLUMNS($A:A)),"")
2	2	2	3	5						In I1:I17 this :=IF(SUM($E1:$H1),IF(OR(SUM(E1:G1)=11,SUM(E1:F1)=11,SUM(F1:G1)=11,SUM(E1,G1)=11),TRUE,""),"")
3	6	3	2	3
4	6	2	0	9	0	2	6	9
5	2	6	5	5
6	5	2	6	3
7	1	5	5	9	1	5	5	9	TRUE
8	2	2	4	4
9	3	4	8	2
10	6	7	4	8
11	8	6	7	1
12	6	3	5	9	3	5	6	9	TRUE
13	5	1	1	2
14	9	2	3	8	2	3	8	9	TRUE
15	5	5	0	7
16	6	5	5	7
17	4	5	6	5

This is nearly fulfill what I am looking for.
need to make some steps to see more clearly.
I am already satisfy the result.

thank you