Solved

Sum Combination Algorithm - Friday Afternoon Puzzle!!

Posted on 2004-10-15
17
595 Views
Last Modified: 2010-08-05
Hi,
Any expert out there ....

I am looking for an algorithm for a VBA-Excel-program.

I have 8 different numbers in an excel spreadsheet row in cells a1,b1,c1,d1,e1,f1,g1,h1 - the number being 10,2,5,6,7,4,1,12

The algorithm should now give all combinations for all the sums i.e. the sum of all 8 numbers, the sum of 7 number in all combinations, sum of 6 numbers in all combinations ..... sum of 2 numbers in all combinations etc

There should be a total of 255 sum combinations, the sum combination being as follows:

                          Combinations
Sum 8 from 8      1
Sum 7 from 8      8
Sum 6 from 8      28
Sum 5 from 8      56
Sum 4 from 8      70
Sum 3 from 8      56
Sum 2 from 8      28
Sum 1 from 8      8
Total Combinations      255


0
Comment
Question by:richardbarry23
  • 9
  • 8
17 Comments
 
LVL 3

Expert Comment

by:dkalel
Comment Utility
This was actually an interesting challenge (yes, I have no life!).  What I came up with uses the fact that the numbers 0 to 255, converted to binary, include eight bits containing every combination of 1's and 0's possible.  Using this fact, I simply go through each number, determine which bits are 1, add the corresponding value to the sum (labeled, interestingly enough "Sum"), increase the bit counter (labeled "Bits", not much originality here), and continue for each of the eight bits.  I then place the value in the corresponding column (using "Bits" to determine how many values were included).  I started the numbers on Row 5, giving you rows 2-4 to include headers, etc.

Well, here it is, tell me what you think:

Sub Sums()
Dim NextRow As Variant, X As Integer, Y As Integer, Sum As Integer, Bits As Integer
NextRow = Array(5, 5, 5, 5, 5, 5, 5, 5)
For X = 1 To 255
    Sum = 0
    Bits = 0
    For Y = 0 To 7
        If X And 2 ^ Y Then
            Sum = Sum + Me.Cells(1, Y + 1)
            Bits = Bits + 1
        End If
    Next Y
    Me.Cells(NextRow(Bits - 1), Bits).Value = Sum
    NextRow(Bits - 1) = NextRow(Bits - 1) + 1
Next X
End Sub
0
 

Author Comment

by:richardbarry23
Comment Utility

Thanks.

I just tried to run the vba and the following error came up:

"Compile Error - Invalid use Me keyword" for the following line "Sum = Sum + Me.Cells(1, Y + 1)"

My Vba coding skills is not that strong so i am not sure what this error means.

0
 
LVL 3

Accepted Solution

by:
dkalel earned 500 total points
Comment Utility
Try Worksheets("SheetName") in place of "Me".  Make sure to us it both places, as follows (replace "SheetName" with the name of the worksheet the data is on, in quotes):

Sub Sums()
Dim NextRow As Variant, X As Integer, Y As Integer, Sum As Integer, Bits As Integer
NextRow = Array(5, 5, 5, 5, 5, 5, 5, 5)
For X = 1 To 255
    Sum = 0
    Bits = 0
    For Y = 0 To 7
        If X And 2 ^ Y Then
            Sum = Sum + Worksheets("SheetName").Cells(1, Y + 1)
            Bits = Bits + 1
        End If
    Next Y
    Worksheets("SheetName").Cells(NextRow(Bits - 1), Bits).Value = Sum
    NextRow(Bits - 1) = NextRow(Bits - 1) + 1
Next X
End Sub
0
 

Author Comment

by:richardbarry23
Comment Utility
Thanks - i made the changes and ran the macro. The following results came out on the SheetName worksheet:
Starting on the corresponding cells A5,B5,C5,D5,E5,F5,G5,H5 - there were 8,28,56,70,56,28,1 zeros listed in each respective column (i.e. being A5:A12; B5:B32; C5:C60;D5:D74;E5:E60; F5:F32; G5:G12; H5:H5).

Assuming i place the data to be summed in  A4,B4,C4,D4,E4,F4,G4,H4 (for instance, being 10,2,5,6,7,4,1,12), instead of the zeros showing up, can the actual individual sums showing up in  A5:A12; B5:B32; C5:C60;D5:D74;E5:E60; F5:F32; G5:G12; H5:H5 ?

Thanks in advance for you patience with my minimal vba coding skills!

 
0
 
LVL 3

Expert Comment

by:dkalel
Comment Utility
Try this.  At the top of your codesheet, before any routines, place the line "Option Explicit".  Then select "Complie..." under the debug menu.  It seems you have 'added' a variable somewhere (probably through a mspelling), which would account for the zeros.

If that isn't it, then cut and paste the code exactly as you have it.  I'm guessing either the "Sum=..." line is wrong, or the data has not been placed in the first row of the spreadsheet.

As far as the rest of your question, you can place the data on any row you wish (up to row four, after that it would get overwritten by the routine).  All you'd have to do then would be to change the "Sum=..." line accordingly.  This will not work to correct your problem, however.
0
 

Author Comment

by:richardbarry23
Comment Utility
Perfect - thanks. I didn't have the data in the first row!!
0
 
LVL 3

Expert Comment

by:dkalel
Comment Utility
Oops, in the second paragraph I meant to say cut and paste the code into a message for me, so I can check it out.
0
 
LVL 3

Expert Comment

by:dkalel
Comment Utility
I assume we're done here :)  I can ride off into the sunset (er...sunrise) now?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:richardbarry23
Comment Utility
Yes - the sun is rising or setting somewhere around the world - thanks for your help
0
 

Author Comment

by:richardbarry23
Comment Utility
Sorry - one final question, I been trying to get the sum data to two decimal places - how do i do that - i should make this 25 more bonus points but i am not sure how to add the bonus points in
0
 
LVL 3

Expert Comment

by:dkalel
Comment Utility
Don't worry about bonus points, here's your answer (I hope):

In this case, the sums will always be integers, so two decimal places would be unnecessary.  There are three ways to do this:

1.  Select the entire sheet, select the "Format" menu, then  "Cells", then "Number", and just assign the cells to two decimal places.  

2.  If you want it in the code, add the following lines to the very end, after the "Next X" line:

    Worksheets("Sheet1").Cells.Select
    Selection.NumberFormat = "0.00"

3.  If you only want the cells with the sums to be formatted this way, just add the following line after the one that starts with "Worksheets...":

    Worksheets("Sheet1").Cells(NextRow(Bits - 1), Bits).NumberFormat = "0.00"

Tell me if this answers your question.  Also, did you understand the code in the accepted answer above?  If not, tell me which part is greek to you, and I'll explain.
0
 

Author Comment

by:richardbarry23
Comment Utility
Thanks - it tried your coding in point 3 and also made variable
Sum to be defined as a Double (not
Integer). All working now. But a quick question on the rounding accuracy - does the summing occur first at the number of decimal place for the inputs and then the results get rounded OR is each individual input in the sum first rounded then summed (i.e. looking at the degree of accuracy in the rounding assumptions)

Thanks again
0
 
LVL 3

Expert Comment

by:dkalel
Comment Utility
Depends on when you round it.  Since the sum isn't written into the spreadsheet until it is completely calculated, the answer is fully accurate for all decimal places.  And formatting the cells does not change the number in the cell at all, just the way it is displayed.  To see what I'm talking about, just format a cell in a spreadsheet to "Number" with two decimal places, then type a number with three decimal places or more into the cell (i.e. "3.256").  You will then see the number rounded to two places in the cell ("3.26") but if you move the cursor to the cell itself, you'll note the original number ("3.256") is actually what's stored in the cell.  Thus, full accuracy is maintained regardless of how you choose to display the result.

If you wanted to round the numbers to two decimal places first, then add them together, simply change the "Sum = ..." line as follows:

            Sum = Sum + Fix(Worksheets("SheetName").Cells(1, Y + 1)*100+.5)/100

"Fix" removes the decimal portion, multiplying y 100 then dividing by 100 ensures the first two decimal places remain, and adding the .5 to the equation ensure rounding is done properly.
0
 

Author Comment

by:richardbarry23
Comment Utility
Understand - thanks for your help, it has been very much appreciated.
0
 

Author Comment

by:richardbarry23
Comment Utility
Sorry for the futher questions. I have spent hours trying to figure out the order of the combination in which the alogorithm produces the sum. Since my Vba skill are not that great, i don't really know what your binary code means.

Is it possible when your alogorithm produce the results, to also record the columns in the base data it has summed and in what order.

For instance, the 8 data points are a1,b1,c1,d1,e1,f1,g1,h1

Sum 8 from 8 =  sum(a1,b1,c1,d1,e1,f1,g1,h1)
Sum 7 from 8 =  sum(a1,b1,c1,d1,e1,f1,g1); sum(b1,c1,d1,e1,f1,g1,h1) etc for all 8 combinations
Sum 6 form 8 =  etc for all 28 combinations

The objective is once the result are produced, to be able to deterine which number were actaual summed in an sum produced by the algorithm (i.e. is it able to leave what columns were summed or better still to formula in the cell like a1+b1+..etc

I don't want to keep asking you questions unrewarded, but if i post another question i am not sure you will see it!!
0
 

Author Comment

by:richardbarry23
Comment Utility
dkalel,

I have figured it out - so not to worry about last question. Thanks
0
 
LVL 3

Expert Comment

by:dkalel
Comment Utility
Sorry, was offline for a couple of days.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now