• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Looking for all combinations to add up to 3

Jack-P-Winner

Jack-P-Winner

Member
Is there an easy way to see how many different combinations there are from four digits 1111 through 9999 that add up to 3?

Example:

1155 = 3

You would add 1+1+5+5 = 12 where 1+2 = 3

I want to know all the four digit combos there could be. Is this possible in a Macro? I can't wrap my brain around it.

Then is there a way to change it to add up to say 4 or 5?

Thanks
 
Hi Larry ,

Do you want to check only the numbers 1111 , 2222 , 3333 ,..., 9999 , or do you want to check all 4 digit numbers from 1111 through 9999 ?

Narayan
 
Narayan,

Unfortunate this wont work that is on Microsoft's page. I only want to do 1111 thru 9999 and this places 0s in the formulas and I was only trying to get the digits that say add up to and switch to say 4
 
Hi Jack,
I have attached a document which may help. You can filter it to get what you want on the last column. If it works copy down to 9999.
Cheers,
 

Attachments

  • sum4digits.xlsx
    10 KB · Views: 13
Hi ,

I do not know but it is possible you have not array entered the formula , using CTRL SHIFT ENTER.

For any 4 digit number , the following formula gives the sum of digits ; this is again an array formula , to be entered using CTRL SHIFT ENTER :

=MOD((INT(SUM(MID(B3,{1,2,3,4},1) + 0)/10) + MOD(SUM(MID(B3,{1,2,3,4},1) + 0),10)-1),9) + 1

For a 4 digit number , the maximum is 9999 , whose initial sum of digits is 36.

The portion of the formula shown in red color isolates the 3 , while the portion shown in blue isolates the 9. The outer MOD now adds these 2 values and restricts the final sum of digits to 9.

Narayan
 
Hi Larry ,

If you are going to have only the 9 values 1111 , 2222 , 3333 , 4444 , 5555 , 6666 , 7777 , 8888 and 9999 , then why do you need any calculations ?

Just do the maths yourself manually and check for which combination gives you 3 , or 4 or which ever value you wish to test for.

1111 will give 4
2222 -------- 8
3333 -------- 3
4444 -------- 7
5555 -------- 2
6666 -------- 6
7777 -------- 1
8888 -------- 5
9999 -------- 9

Thus , the only combination which gives 3 is 3333.

All you have to do is use an IF statement , as follows :

=IF(number = 3333 , 1 , 0)

Narayan
 
Here is an example of done of the 4 digit numbers that add up to 3. I'm looking for a way to find "all" the 4 digit numbers available that can add up to numbers 1-9 without ANY 0s in the 4 digits. Hope this helps. Focus on the numbers in yellow and gray cells only with red and green font
 

Attachments

  • image.jpg
    image.jpg
    138.2 KB · Views: 11
Perhaps I need to say this differently. I want to click on a button and it have a selection to add the SUMS from 1-9 If I select the 3 and then tell the Macro to run it will produce "all" the 4 digit combinations that add uo to 3 as in my example in the pic. Now, this pic has duplication because i just grabbed it from one of my sheets so my apologies

If it can be done with formulas that is okay too but i am not sure how. Hopefully this makes better sense

Here is a sample file

http://www.mediafire.com/download/o2plamtjp39aahm/Sample_of_digit_3.xlsx
 

Attachments

  • 2016-08-14_22-52-48.jpg
    2016-08-14_22-52-48.jpg
    350.2 KB · Views: 3
Hi @@Jack-P-Winner,

If you avoid the number 0,You have 6561 combinations,9^4,and after root sum add up to 3 ,that's about one-third,2187 ,this combination left.

Do you have Excel, a table of combinations, 10000 or 6561?
If you do not, it's not a problem to build it, I can do that for you.

In other words, get a filter that you want from a table of all the existing combinations.

Only I did not understand something, whether formula I gave earlier, that's okay?

David
 
Last edited:
If you don't have an explicit list of the integers from 1111 to 9999 anywhere within the actual workbook, it's probably best to store these values as an array constant within Name Manager so as to avoid repeated calculation of the necessary clause within each iteration of the main formula, i.e.:

Name: Arry1
Refers to: =ROW($1111:$9999)

Depending upon whether you intend to vary the parameters within this set-up, it might also be worthwhile using a single cell somewhere to record the expected number of returns, so as to avoid a resource-heavy IFERROR construction for the main formula (assuming you wish to mask errors in rows beyond the expected number of returns, that is), e.g. in B1:

=SUMPRODUCT(0+(MOD(Arry1-1,9)=2),1-ISNUMBER(FIND(0,Arry1)))

after which the main array formula**, placed in your cell of choice, is:

=IF(ROWS($1:1)>B$1,"",SMALL(IF(1+MOD(Arry1-1,9)=3,IF(1-ISNUMBER(FIND(0,Arry1)),Arry1)),ROWS($1:1)))

and copied down until you start to get blanks for the results.

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).
 
bines53 said:
Hi @@Jack-P-Winner,

If you avoid the number 0,You have 6561 combinations,9^4,and after root sum add up to 3 ,that's about one-third,2187 ,this combination left.

Do you have Excel, a table of combinations, 10000 or 6561?
If you do not, it's not a problem to build it, I can do that for you.

In other words, get a filter that you want from a table of all the existing combinations.

Only I did not understand something, whether formula I gave earlier, that's okay?

David
Click to expand...
I can handle the 10000, I have an Icore7 with 2 solid state drives one with Excel only plus 32 GIGS of RAM allocated to Excel alone. Not sure how to build it as of yet, will require some reading. If it's easy for you to build it would be much appreciative

Thsnks
 
XOR LX said:
If you don't have an explicit list of the integers from 1111 to 9999 anywhere within the actual workbook, it's probably best to store these values as an array constant within Name Manager so as to avoid repeated calculation of the necessary clause within each iteration of the main formula, i.e.:

Name: Arry1
Refers to: =ROW($1111:$9999)

Depending upon whether you intend to vary the parameters within this set-up, it might also be worthwhile using a single cell somewhere to record the expected number of returns, so as to avoid a resource-heavy IFERROR construction for the main formula (assuming you wish to mask errors in rows beyond the expected number of returns, that is), e.g. in B1:

=SUMPRODUCT(0+(MOD(Arry1-1,9)=2),1-ISNUMBER(FIND(0,Arry1)))

after which the main array formula**, placed in your cell of choice, is:

=IF(ROWS($1:1)>B$1,"",SMALL(IF(1+MOD(Arry1-1,9)=3,IF(1-ISNUMBER(FIND(0,Arry1)),Arry1)),ROWS($1:1)))

and copied down until you start to get blanks for the results.

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).
Click to expand...


Not worked with Name a Manger much but I'll do some studying. What you gave me is much appreciative. Am I Asking a lot for you to get a worksheet started for me that I can finish so I can see exactly what your talking about?

Thanks
 
I'm sure that, after a few minutes of research, you'll understand what I mean by Name Manager.

However, if you're still struggling, you could use this variation:

In B1:

=SUMPRODUCT(0+(MOD(ROW($1111:$9999)-1,9)=2),1-ISNUMBER(FIND(0,ROW($1111:$9999))))

Main array formula**:

=IF(ROWS($1:1)>B$1,"",SMALL(IF(1+MOD(ROW($1111:$9999)-1,9)=3,IF(1-ISNUMBER(FIND(0,ROW($1111:$9999))),ROW($1111:$9999))),ROWS($1:1)))

though be aware that this will be less efficient.

Of course, even better is to simply enter the numbers 1111 to 9999 into the actual workbook somewhere, e.g. in Sheet2A1:A8889, after which the formulas (assumed to be in a sheet other than Sheet2) are:

In B1:

=SUMPRODUCT(0+(MOD(Sheet2!A$1:A$8889-1,9)=2),1-ISNUMBER(FIND(0,Sheet2!A$1:A$8889)))

Main array formula**:

=IF(ROWS($1:1)>B$1,"",SMALL(IF(1+MOD(Sheet2!A$1:A$8889-1,9)=3,IF(1-ISNUMBER(FIND(0,Sheet2!A$1:A$8889)),Sheet2!A$1:A$8889)),ROWS($1:1)))

Regards
 
Hi @Jack-P-Winner,

Hi XOR LX ,My friend, good to hear from you !

Cell C1, the total possible combinations,

Column A, all the combinations.

I copied the formula to line number 2262, if necessary, you can copy it to the end.


David
 

Attachments

  • 4 digit.xlsb
    77.6 KB · Views: 10
Last edited:
Hi XOR LX ,

There is almost no difference in speed between SMALL(IF to AGGREGATE,

I got used to use the function AGGREGATE.

You can use it almost every time !

David
 
You must log in or register to reply here.
Back
Top