[SOLVED] Split number into sum of powers of two

Split number into sum of powers of two

I am trying to work out how to make a geometric sequence calculator of sorts.

With the products I work with, our fault codes are made up of a geometric sequence up to 128 (1, 2, 4, 8, 16, 32, 64, 128). These codes are then summed to generate a single fault code. I need a way to, given the sum, get back the fault codes that were used to generate it.

For example, if I get 108, I want to make a calculator that will automatically take the number and break it up into 64+32+8+4.

I am not even sure how to start this. Any suggestions?

Solution

Alternative by tokenizing a binary number equivalent and negative filtering

With Function adds() I want to demonstrate a way how to get the addends of a geometric base 2 sequence, which are represented in this answer by the digits of an equivalent binary number string. - Furthermore my answer doesn't narrow the input to numbers lower than 512.

Needed steps:

a) provide for sufficient array elements,
b) calculate addends based on binary equivalent
c) filter out empty (zero) values via negative filtering and return function result as string joined by plus signs +.

Methodical hint

I prefer a direct calculation of the binary code string overriding Excel's very low input limitation (input < 512 only!) when using WorksheetFunction.Dec2Bin(). Here this is done by subsequent number division by base 2 counting all modulo rests as digit values.

Side note: Another workaround is shown by @cdp1802 answer using VBA's Hex() function as intermediate value and converting each digit/letter into a group of 4 binary digits.

Function adds(ByVal number As Long, Optional bInfo As Boolean = True) As String
    Const BASE = 2
'a) provide for sufficient array elements 
    Dim h As String: h = CStr(Hex(number))
    ReDim arr(0 To 4 * Len(h) - 1)
'b) calculate addends
    Dim i As Long, bin As String
    For i = LBound(arr) To UBound(arr)               ' assign array elements
        arr(i) = IIf(number Mod BASE, BASE ^ i, " ") ' replace digit 1 by 2 ^ i (power)
        bin = CStr(number Mod BASE) & bin            ' for optional display only :-)
        number = number \ BASE                       ' integer division
    Next i
    If bInfo Then
        Debug.Print "hex .. " & h
        Debug.Print "bin .. " & bin
    End If
'c) return positive addends only (via negative filtering)
    adds = Join(Filter(arr, " ", False), "+")
End Function

Example call

Sub ExampleCall()
    Dim number As Long
    number = 108
    Debug.Print "dec .. " & number
    Dim addends As String
    addends = adds(number)
    'Display Results
    Debug.Print "   ~~> " & addends & " = " & Evaluate(addends)
End Sub

Result in VB Editor's immediate window:

dec .. 108
hex .. 6C
bin .. 01101100
   ~~> 4+8+32+64 = 108

Further hints due to comment as of 2025-07-17

Splitting a decimal number into sum of powers of two is nothing other than

1. getting the binary number equivalent and
1. translating each binary digit (0 or 1) into a decimal value (defined by its order within the binary number),
1. isolating the positive values, 4) concatenating These values to a result string.

ad 1)

An old and effective method to get a binary number is the following which btw avoids the internal conversion limit of Excel:

a) Divide decimal number by 2 (integer division) and note the remainder. b) Repeat the (integer) divisions with each result till the remaining result gets 0. c) Concatenate the rest values (0 and 1 digits) bottom up.

Example

Applied to function adds() the interesting values are entered into an array arr(0 To 7), here consisting of 8 elements in the given case (the needed upper boundary 7 can be calculated as 4 times the length of the corresponding hex string minus 1, = 4 * length of string "6C" - 1 = 7):

 108          arr(0)=" " 
 54           arr(1)=" " 
 27           arr(2)=4
 13           arr(3)=8
 6            arr(4)=" "
 3            arr(5)=32
 1            arr(6)=64
 0            arr(7)=" "

The array elements arr(i) receive only positive digits (1) replacing these by the corresponding sequence value and replacing zero digits (0) by a space.

Having completed the array loop it is a simple thing to join only the non-blank elements via negative filtering, to add a delimiter "+" and return the resulting string as function result:

adds = Join(Filter(arr, " ", False), "+")

Note: The bin array serves for optional display demonstrating how to get the binary equivalent; the purpose of the function adds() here is only to show the concatenated values connected by "+".