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?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

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
Back
Top