How to ID digits within a group of 8-digits that equal a specified number

[Thunder001]

I have a list of eight digit numbers, example: 05-08-09-10-12-16-20-45
This list can be within one cell or one-cell-per-digit.

Let's say I want to know, within my list of eight digit numbers, which two digits can either add or subtract to 28.
In this example I know the only 2-digits that can add to 28 are 08 & 20. I want excel to display the digits that add/subt to the
any number I want. Whether I want to know which 2-digits add/subt to 15 or 43 or 11.., I want excel to display the actual digits,
within my group of eight digits, adjacent to my list of eight-digit numbers.

Thank you for your help on this.

Ron

[XOR LX]

Hi,

Don't 16 and 12 from that list also add up to 28?

If you're not concerned which pair of numbers is chosen in such cases, and assuming your list is in A1:H1 with choice of target value, e.g. 28, in J1, then:

In K1, array formula**:

=MAX(IF(A1:H1<>TRANSPOSE(A1:H1),IF((A1:H1+TRANSPOSE(A1:H1)=J1)+(A1:H1-TRANSPOSE(A1:H1)=J1),A1:H1)))

In L1:

=IF(K1=0,0,ABS(J1-K1))

Both these cells displaying zero indicates that there are no solutions. Of course, this assumes that solutions where one of the numbers is zero (e.g. 0 & 28) are disallowed.

I notice your profile says Excel 97. To the best of my knowledge the functions I have used are available in that version, though I'm afraid I can't be certain.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[XOR LX]

P.S. I should also have mentioned that I made the assumption that the numbers within the list are unique.

Regards

[Thunder001]

Thank you for your response. It was an oversight I did not notice 12 and 16 also equal 28. Sometimes there can be duplicate digits in the list.
Based to what your response; I used the eight-digit example and entered it one-digit-per-cell A1 through H1. In J1 I entered "28".
In K1 In entered:
=MAX(IF(A1:H1<>TRANSPOSE(A1:H1),IF((A1:H1+TRANSPOSE(A1:H1)=J1)+(A1:H1-TRANSPOSE(A1:H1)=J1),A1:H1)))
In L1 in entered: =IF(K1=0,0,ABS(J1-K1))
I held down both Shift & Ctrl and hit Enter. Nothing happens. I only see #Value! in K1 and L1.
I must have left something out. What could that be?

Ron

[XOR LX]

Re-read my message about how to enter array formulas, and note that this applies only to the first formula, not the second.

As I mention in the instructions re array formulas, you'll know you've done it correctly when you see the curly brackets appear around the formula.

Regards

[Thunder001]

Ok, I got it to work. Thank you! But only 08 and 20 appear. What about 12 and 16?

[XOR LX]

Ok, I got it to work. Thank you! But only 08 and 20 appear. What about 12 and 16?

You didn't say that you wanted all solutions to be displayed.

As I explained in my original post:

If you're not concerned which pair of numbers is chosen in such cases...

Regards

[Thunder001]

But can the formula you gave me be modified so it does display all solutions?

[XOR LX]

That's a lot more difficult. I've got to go now but will have a look again tomorrow.

Cheers

[Thunder001]

Thank you very much for all your help on this.

[XOR LX]

Can you try this and get back to me?

An additional array formula** in J2, used to count the expected number of returns:

=SUM(IF(COLUMN(A$1:H$1)>TRANSPOSE(COLUMN(A$1:H$1)),IF((A$1:H$1+TRANSPOSE(A$1:H$1)=J$1)+(ABS(A$1:H$1-TRANSPOSE(A$1:H$1))=J$1),1)))

Then change the array formula** in K1 to:

=IF(ROWS(A$1:A1)>J$2,"",SMALL(IF(COLUMN(A$1:H$1)>TRANSPOSE(COLUMN(A$1:H$1)),IF((A$1:H$1+TRANSPOSE(A$1:H$1)=J$1)+(ABS(A$1:H$1-TRANSPOSE(A$1:H$1))=J$1),A$1:H$1)),ROWS(A$1:A1)))

and the formula in L1 to:

=IF(K1="","",IF(K1=0,0,ABS(J$1-K1)))

Copy the formulas in K1 and L1 down until you start to get blanks for the results.

Again, note that this assumes that the entries in A1:H1 are unique.

Cheers

[Thunder001]

Yes I got it to work. I very much admire the desire you have to help people!
By the way, I duped one of the digits used to add to 28 to see what would happen. All that happens is that it lists the 2-digits equaling 28 twice instead of once.
That not a problem for me.

Thank you again!!

[Bo_Ry]

I have another method.

Number at A1:H1
Sum result at J1

L1:R1

=IFERROR(1/(1/SUMPRODUCT(A1*((A1+OFFSET(B1,,,,COLUMNS(B1:$H1)))=$J$1)))&"+"&$J$1-A1,"")

[XOR LX]

Hi Bo,

How are you accounting for subtraction? For example, if we put the numbers 1-7 in A1:G1 and then 35 in H1, with a target value of 28, we should get 35 and 7 as an answer, which my formula gives.

Let's say I want to know, within my list of eight digit numbers, which two digits can either add or subtract to 28.

Regards

[Bo_Ry]

Hi XOR LX,

I did't see subtract.

L1:R1 for add or subtract

Please Login or Register  to view this content.

[XOR LX]

No IFERROR in Excel 97!

In fact, problems such as this are certainly not straightforward using Excel 97 functions alone!

Cheers

[Glenn Kennedy]

I don't remember Excel 97... and I'm ancient. I must be going senile.

[Bo_Ry]

Oh! I never see Excel 97

Then without if error

=IF(SUMPRODUCT(A1*((A1+OFFSET(B1,,,,COLUMNS(B1:$H1)))=$J$1)),A1&"+"&$J$1-A1,IF(SUMPRODUCT(A1*((OFFSET(B1,,,,COLUMNS(B1:$H1))-A1)=$J$1)),$J$1+A1&"-"&A1,""))

[Thunder001]

I am going to examine your updates later today and get back to you. Again, I very much appreciate your attention to my needs!

[XOR LX]

Apologies. Just noticed that my formula for column L was not rigorous.

Replace the formula in L1 with the following array formula**:

=IF(K1="","",SMALL(IF((A$1:H$1+K1=J$1)+(ABS(A$1:H$1-K1)=J$1),A$1:H$1),SUM(0+(K$1:K1=K1))))

One example of where my previous set-up failed (as I believe does Bo_Ry's) is the following:

45 44 10 14 12 16 17 13

with 28 in J1.

The solutions should be: 16&12, 16&44 and 17&45.

This should be the final version now, hopefully!

Cheers

[Bo_Ry]

Fixed.

L1:S1

=IF(SUMPRODUCT(N(A1+B1:$H1=$J$1)),A1&"+"&$J$1-A1,IF(COUNTIF($K1:K1,IF(SUMPRODUCT(N(ABS($A$1:$G$1-A1)=$J$1)),IF(A1>$J$1,A1&$J$1-A1,$J$1+A1&-A1),"")),"",IF(SUMPRODUCT(N(ABS($A$1:$G$1-A1)=$J$1)),IF(A1>$J$1,A1&$J$1-A1,$J$1+A1&-A1),"")))

[XOR LX]

Hi again,

I now realise that the previous solutions were not correct.

In fact, this is much more difficult than I previously thought. What's more, the fact that you are using Excel 97 complicates matters further: not only does that make inadmissible solutions which use functions available only in later Excel versions, but the limit on nesting within formulas means that we have to use multiple Named Ranges to circumvent that limit.

Assuming that we now have 12 values (in B1:M1), instead of the previous 8, with your Target Value now in O1, and that you now wish to see any single values which equal the target value also returned as a solution in the form "XX-##" (where "XX" represents the target value), first go to Name Manager and make the following definitions:

Name: MyRange
Refers to: =$B$1:$M$1

Name: MyTarget
Refers to: =$O$1

Name: MyColumns
Refers to: =COLUMNS(MyRange)

Name: Arry1
Refers to: =MyRange>TRANSPOSE(MyRange)

Name: Arry2
Refers to: =0+(MyRange&"."&TRANSPOSE(MyRange))

Name: Arry3
Refers to: =0+(MyRange&".0")

Name: Arry4
Refers to: =(COLUMN(MyRange)<>TRANSPOSE(COLUMN(MyRange)))*(MyRange+TRANSPOSE(MyRange)=MyTarget)+(ABS(MyRange-TRANSPOSE(MyRange))=MyTarget)

Name: Arry5
Refers to: =FREQUENCY(IF(MyRange=MyTarget,Arry3,IF(Arry4,IF(Arry1,Arry2,0+(TRANSPOSE(MyRange)&"."&MyRange)))),IF(MyRange=MyTarget,Arry3,IF(Arry4,IF(Arry1,Arry2,0+(TRANSPOSE(MyRange)&"."&MyRange)),88^88)))

Name: MyCount
Refers to: =SUM(IF(Arry5,1))

The required array formula** in P1 is then:

=IF(ROWS(A$1:A1)>MyCount,"",INDEX(MyRange,1+MOD(SMALL(IF(Arry5>0,ROW(INDIRECT("1:"&MyColumns^2))),ROWS(A$1:A1))-1,MyColumns)))

And the required array formula** in Q1 is:

=IF(P1="","",IF(P1=MyTarget,"##",INDEX(MyRange,1+INT((SMALL(IF(Arry5>0,ROW(INDIRECT("1:"&MyColumns^2))),ROWS(A$1:A1))-1)/MyColumns))))

Copy both down until you start to get blanks for the results.

See the attached file.

Regards

[Thunder001]

Hello XOR LX,

Are the updates you have listed already in the excel-file you attached?

Thanks,

Ron

[XOR LX]

Yes, the attached file includes the updates you requested. Please test for yourself and get back to me with any observations.

Regards

[Thunder001]

Yes I will my good friend!

[FlameRetired]

Thunder001,

In the meantime see if this solution by José Augusto can be adapted to your needs.

List All Combination Results Post#12

Also so far you have mentioned a pool of 8 numbers and XOR LX has introduced 12. What is the maximum number you anticipate testing?

[XOR LX]

In the meantime see if this solution by José Augusto can be adapted to your needs.

List All Combination Results Post#12

Flame - OP is using Excel 97 (!), so not sure if José's solution can be adapted?

But point taken - I confess that this could be made much simpler with the use of helper columns. I'm guilty of being too fascinated with attempting to create the required arrays in-formula.

Cheers

[Thunder001]

XOR LX

I have tested it several times and I don't see anything wrong with it. You did a perfect job. I stand a little confused why you're not working for NASA.
You should continue to develop it as some kind of reverse-calculator something. Only thing I would change is something that's my fault NOT YOURS. If the target was the same digit as one of the 12 I wanted it to list it as ## 0. (## equals what ever the number may be is next to a zero). But that's not important, I did not describe it properly. I only bring it up so that if you ever come up with any practical applications for it yourself, its better to use the zero.

THANK YOU !!!!!!!!!!!!!!!!!!!!!!!!!!

[FlameRetired]

Flame - OP is using Excel 97 (!), so not sure if José's solution can be adapted?

But point taken - I confess that this could be made much simpler with the use of helper columns. I'm guilty of being too fascinated with attempting to create the required arrays in-formula.

Cheers

Thank you. I was thinking more in terms of the helper ranges. I completely overlooked mentioning the AGGREGATE wouldn't work.

I too am guilty of fascination with arrays and overlooking helper columns.

This is also a favorite problem type of mine. Been looking for the "shorter route" for about a year now --- ever since José posted that. I've made little progress.

[XOR LX]

I have tested it several times and I don't see anything wrong with it. You did a perfect job.

Glad to know that it's working ok!

I stand a little confused why you're not working for NASA.

I would hope that they're not also still using Excel 97!

Thanks for the feedback and let me know if you need anything tweaking in future.

Cheer

[Thunder001]

I definitely will! Please keep a copy file of what you've done. I actually just ordered Office 2003 on Ebay. Do you think it can convert the 97-file without adversely effecting the formulas?

Ron

[XOR LX]

Yes - it's going the other way that might present problems.

Regards