Indexing array that's decypherable from permutation of its sums

iknowu99

Well-known Member
Joined
Dec 26, 2004
Messages
1,158
The goal is to create index that would in any permutation create unique sums.

If these five numbers are used then any permutation of the sum will be decipherable

1
11
111
1,111
11,111

Example1: if sum was 123 it means the first three numbers were used.

example2: if sum was 11,222 then it means third (111) and fifth (11,111) numbers were used to create the sum.

example3: if sum was 11,223 then first, third and fifth numbers were used.

This system will work and it does not matter how many indexed numbers (here five). The issue is that the numbers increase too fast. What is better index of the smaller distance between indexing?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

iknowu99

Well-known Member
Joined
Dec 26, 2004
Messages
1,158
If it was binary first six digits would be
1
10
11
100
101
110

and if for ex: sum=110 we couldn't tell if it was the sixth item or second and fourth. I don't think it's binary.
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,708
Binary could only be 2,3,4,6,7, but I suspect I'm missing something, so Sorry, I'll bow out I've no idea what you're after.
 

iknowu99

Well-known Member
Joined
Dec 26, 2004
Messages
1,158

ADVERTISEMENT

Binary could only be 2,3,4,6,7, but I suspect I'm missing something, so Sorry, I'll bow out I've no idea what you're after.

not sure what 2,3,4,6,7 means.

My apologies if not clear but what I am after is counting system for which there will be unique sums. I know I can use the units, tens, hundreds, etc. to figure out which numbers in the array were used to make the sum (as in the first example where 11,223 was created using first, third and fifth numbers). But the issue will be that I run out of numbers really quickly if I try to index 30 items for example. So I am trying to create index that creates distinct sums for which I can use algorithm to return array of the numbers that were used, in that case it was (1,3,5).
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
If each number is used only once in the sum, use a binary system, the base numbers would be 1, 2, 4, 8, 16

in binary they would be represented as 00001 00010 00100 01000 10000

The binary represnation of a sum would be a string of 0's and 1's

so if the (bin) reprentaion of the sum is 10101, we know that the first, third and fifth numbers made up the sum

if the (binary) representation of the sum is 01011, first second and fourth.

If this is for a VBA routine you could use the bit wise AND and OR for testing

Code:
MyArray(1)=1, myArray(2) = 2, myArray(3)=4, myArray(4)=8, myArray(5)=16


If (MyArray(I) And mySum) <> 0 then
    MsgBox "the ith element was used in the sum"
End If

If each element is used once, at most, in the sum.
 
Last edited:

iknowu99

Well-known Member
Joined
Dec 26, 2004
Messages
1,158

ADVERTISEMENT

If each number is used only once in the sum, use a binary system, the base numbers would be 1, 2, 4, 8, 16

in binary they would be represented as 00001 00010 00100 01000 10000

The binary represnation of a sum would be a string of 0's and 1's

so if the (bin) reprentaion of the sum is 10101, we know that the first, third and fifth numbers made up the sum

if the (binary) representation of the sum is 01011, first second and fourth.

If this is for a VBA routine you could use the bit wise AND and OR for testing

Code:
MyArray(1)=1, myArray(2) = 2, myArray(3)=4, myArray(4)=8, myArray(5)=16


If (MyArray(I) And mySum) <> 0 then
    MsgBox "the ith element was used in the sum"
End If

If each element is used once, at most, in the sum.


you're awesome!


Code:
Function DecToBin(ByVal DecimalIn As Variant, Optional NumberOfBits As Variant) As String
  DecToBin = ""
  DecimalIn = CDec(DecimalIn)
  Do While DecimalIn <> 0
    DecToBin = Trim$(Str$(DecimalIn - 2 * Int(DecimalIn / 2))) & DecToBin
    DecimalIn = Int(DecimalIn / 2)
  Loop
  If Not IsMissing(NumberOfBits) Then
    If Len(DecToBin) > NumberOfBits Then
      DecToBin = "Error - Number too large for bit size"
    Else
      DecToBin = Right$(String$(NumberOfBits, "0") & _
      DecToBin, NumberOfBits)
    End If
  End If
End Function


Public Function retArray(ByVal DecimalVIn As Variant) As String
    
    
    bnryVal = DecToBin(DecimalVIn)
        
        For i = 1 To Len(bnryVal)
        tempVal = Right(bnryVal, 1)
        If tempVal = "1" Then
                If cntr = 0 Then
                retArray = i
                Else
                retArray = retArray & "," & i
                End If
            cntr = cntr + 1
            
        End If
        
        bnryVal = Left(bnryVal, Len(bnryVal) - 1)
        
        Next i
        
End Function
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,708
That what I was trying to say, only not as well as Rick :(
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
I'm not sure where you are going with this but, it looks like your retArray function is being given a number like 11 (binary 1011) and returning the string "1, 2, 4"

A simple way to do that is

Code:
Function BitsUsed(aNumber As Long) As String
    Dim i As Long
    Dim limit As Long
    
    For i = 0 To Int(Log(aNumber) / Log(2))
    
        If CBool((2 ^ i) And aNumber) Then
            BitsUsed = BitsUsed & ", " & (i + 1)
        End If
        
    Next i
    
    BitsUsed = Mid(BitsUsed, 3)
End Function

Also your DecToBin could be replaced with the WorksheetFunction.Dec2Bin.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,089
Messages
5,599,667
Members
414,326
Latest member
Aerith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top