if the sum of 2 numbers equal to another number

[excelNewbie22]

hi!
i need to know if the sum of any 2 numbers in c2:h2 equal to some number within the same range?
for example c2+g2=h2
also, if the sum of any 2 numbers in c3:h3 equal to some number in c2:h2?
for example c3+f3=h2
don't need to know which, just if
if there's mark with 1 if not, with 0
if there's one formula that can save the helper one in i col, the better, if not, also good

test.xlsm
	C	D	E	F	G	H	I
2	1	11	22	25	30	31	12,33,23,47,36,26,55,52,41,31,61,56,53,42,32
3	1	2	4	30	32	33	3,6,5,34,32,31,62,36,34,33,65,63,37,35,34
4	2	5	12	18	26	40	7,17,14,30,23,20,44,38,31,28,66,58,52,45,42
test

Cell Formulas
Range	Formula
I2:I4	I2	=D2+C2&","&E2+D2&","&E2+C2&","&F2+E2&","&F2+D2&","&F2+C2&","&G2+F2&","&G2+E2&","&G2+D2&","&G2+C2&","&H2+G2&","&H2+F2&","&H2+E2&","&H2+D2&","&H2+C2

 

[Eric W]

There's probably a better way, especially if the numbers are always ascending, but try:

Book1
	C	D	E	F	G	H	I
2	1	11	22	25	30	31	1
3	1	2	4	30	32	33	1
4	2	5	12	18	26	40	0
Sheet2

Cell Formulas
Range	Formula
I2:I4	I2	=LET(r,C2:H2,s,SEQUENCE(64,,0),h,DEC2BIN(s,6),w,LEN(SUBSTITUTE(h,"0","")),t,MID(h,{1,2,3,4,5,6},1)+0,m,MMULT(t*r,{1;1;1;1;1;1})*(w=2),MAX(--ISNUMBER(MATCH(m,r,0))))

 

[excelNewbie22]

thank you eric, it works!

what about

if the sum of any 2 numbers in c3:h3 equal to some number in c2:h2?

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J
1									same group	different groups
2			1	11	22	25	30	31	1	0
3			1	2	4	30	32	33	1	1
4			2	5	12	18	26	40	0	1
5
Sheet2

Cell Formulas
Range	Formula
I2:I4	I2	=LET(r,C2:H2,s,SEQUENCE(64,,0),h,DEC2BIN(s,6),w,LEN(SUBSTITUTE(h,"0","")),t,MID(h,{1,2,3,4,5,6},1)+0,m,MMULT(t*r,{1;1;1;1;1;1})*(w=2),MAX(--ISNUMBER(MATCH(m,r,0))))
J2:J4	J2	=LET(ra,C2:H2,rb,C1:H1,n,2,c,COLUMNS(ra),s,SEQUENCE(2^c,,0),h,DEC2BIN(s,c),w,LEN(SUBSTITUTE(h,"0","")),t,MID(h,SEQUENCE(,c),1)+0,m,MMULT(t*ra,SEQUENCE(c,,,0))*(w=n),MAX(--ISNUMBER(MATCH(m,rb,0))))

I generalized the second formula a bit. You can define two ranges, one for the numbers to add, one for the numbers to check. You can change the number of columns just by changing the range (C2:H2 to B2:H2 for example), although you'll probably hit a limit pretty quick. You can also change the number of numbers to add from 2 to whatever you want.

Hope this helps!

 

[KRice]

Very interesting approach, Eric. Could you have a look at the generalized formula? I believe the last function (MATCH) should be looking for "m" in "ra" rather than "rb". I'm not sure why "rb" is needed at all.

 

[excelNewbie22]

many thanks!
kudos!

 

[excelNewbie22]

with your permission eric, another question if you may,
is it possible to alter the formula in j2 to check the
difference in c3:h3 between any two numbers
against one in c2:h2
instead the sum ?

also, you said

I generalized the second formula a bit. You can define two ranges, one for the numbers to add, one for the numbers to check. You can change the number of columns just by changing the range (C2:H2 to B2:H2 for example), although you'll probably hit a limit pretty quick. You can also change the number of numbers to add from 2 to whatever you want.

did you meant i can check c4:h4 against c3:h3 and c2:h2 , separately, in one formula?
cause i didn't manage to do it
just replacing the range number

 

[Eric W]

Very interesting approach, Eric. Could you have a look at the generalized formula? I believe the last function (MATCH) should be looking for "m" in "ra" rather than "rb". I'm not sure why "rb" is needed at all.

Because the second part of the OP's question was:
also, if the sum of any 2 numbers in c3:h3 equal to some number in c2:h2?
where we're checking the sum of 2 numbers in one range against the numbers in another range.

is it possible to alter the formula in j2 to check the
difference in c3:h3 between any two numbers
against one in c2:h2
instead the sum ?

Hmm, much more complicated, I'll think about it.

did you meant i can check c4:h4 against c3:h3 and c2:h2 , separately, in one formula?

No, the formula only checks one row of sums with another row of numbers. If you want to compare one row against the 2 previous rows, or the 3 previous rows, or even all previous rows, I think I can do that if you want.

 

[KRice]

Thanks for following up with an explanation to my question, Eric. I see that I misinterpreted rb. For some reason, I was assuming (incorrectly) that rb would always be a horizontal array of 0's, but that is the case only for the first row of values to be evaluated when the blank cells in row 1 are referenced. So your formula compares combinations of sums (of any 2 values on that row) to the range immediately above and "rb" will update to reference the range immediately above as the formula is pulled down the sheet.

At the risk of overcomplicating things, I suppose any contiguous range of cells that might be of interest for the final comparison could be stacked into a single vertical array and the match performed. For example, if rb were defined as:

=SORT(UNIQUE(LET(array,C2:H3,rowcount,ROWS(array),colcount,COLUMNS(array),itemcount,rowcount*colcount,MAKEARRAY(itemcount,1,LAMBDA(r,c,INDEX(array,LET(a,MOD(r,rowcount),IF(a=0,rowcount,a)),ROUNDUP(r/rowcount,0))  )))))

...we would get the range C2:H3 stacked into a single vertical array, and the final matching done by MAX(--ISNUMBER(MATCH(m,rb,0))) could answer both questions (is there a match on the current row or the row above) in one step.

If this is of interest, the single-step, multiple row match adjustment would look like this:

=LET(ra,C2:H2,rb,SORT(UNIQUE(LET(array,C2:H3,rowcount,ROWS(array),colcount,COLUMNS(array),itemcount,rowcount*colcount,MAKEARRAY(itemcount,1,LAMBDA(r,c,INDEX(array,LET(a,MOD(r,rowcount),IF(a=0,rowcount,a)),ROUNDUP(r/rowcount,0))  ))))),n,2,c,COLUMNS(ra),s,SEQUENCE(2^c,,0),h,DEC2BIN(s,c),w,LEN(SUBSTITUTE(h,"0","")),t,MID(h,SEQUENCE(,c),1)+0,m,MMULT(t*ra,SEQUENCE(c,,,0))*(w=n),MAX(--ISNUMBER(MATCH(m,rb,0))))

 

[Eric W]

At the risk of overcomplicating things, I suppose any contiguous range of cells that might be of interest for the final comparison could be stacked into a single vertical array and the match performed. For example, if rb were defined as:

Yes, I think you got the gist of it now. I don't have the MAKEARRAY function yet, so I was going to do something like this:

rb,SMALL(C2:H2,SEQUENCE(COUNT(C2:H2))),

to put the previous rows in a vertical array. That's untested, but it should work. For numeric values of course. As far as the latest request (difference instead of sum), I'm not sure I have a good solution for that. The formula is complicated enough, I think at this point it's looking like a UDF would be better. Let's see what the OP says.