New Contributor

# Working with arrays

Hi guys I´m not the best in using excel files, I would really apriciate some help with my university task. Thanks a lot !

Title: Arrays

Read any integer from the worksheet and convert it to a binary value to be stored in the array. Example:

Number = 10

Binary representation = [1,0,1,0]

The algorithm for converting to binary is as follows:

• Step 1: Divide the number by 2 through % (modulus operator) and store the remainder in array.
• Step 2: Divide the number by 2 through / (division operator)
• Step 3: Repeat the step 2 until number is greater than 0.
5 Replies

# Re: Working with arrays

This may not be the best way but it divides by two and counts remainders until there is nothing left. N divides by two, M carries that down, and O looks if its even (there will be no remainder so set to 0, otherwise print 1). The concat strings the binary digits together, and the result formula reverses the concat result to get the final binary number.

Here is the formula view, a bity messy but it works! It goes past the 512 limit set by excel =DEC2BIN function.

M: =+N52

N: =+ROUNDDOWN(M53/2,0)

O: =+IF(M53=0,"",IF(ISEVEN(M53)=TRUE,0,1))

Concat: =+CONCAT(O52:O65)

Result: =TEXTJOIN("",1,MID(R52,ABS(ROW(INDIRECT("1:"&LEN(R52)))-(LEN(R52)+1)),1))

# Re: Working with arrays

That could be like this

# Re: Working with arrays

You may need to clarify whether you're looking for a formula solution or a vba solution? I believe "%" is a modulo operator in some other programming languages, but I'm not aware of it being the used for such in Excel/VBA.

If you're looking at vba, then perhaps something like this (but instead of using the division operator, I'm using the integer division operator "\" - I don't think it will work correctly with just "/" operator unless you wrap it with the Int function):

``````Public Function DecToBinary(ByVal decNum As Long) As Variant
Dim binNum() As String: ReDim binNum(0)
Dim intPart As Integer: intPart = decNum
Dim modPart As Integer

Do
modPart = intPart Mod 2
intPart = intPart \ 2
binNum(UBound(binNum)) = modPart

If intPart > 0 Then
ReDim Preserve binNum(UBound(binNum) + 1)
End If

DoEvents
Loop Until intPart = 0

DecToBinary = Split(StrReverse(Join(binNum, ";")), ";")

End Function``````

# Re: Working with arrays

It would make sense to use a built-in function for this if possible.  If the limits of DEC2BIN are a problem the function BASE appears remove the limitation

``= BASE(number,2)``

If the objective is to express the result as a bit array rather than a string, LET allows a well structured solution

``````= LET(
bin, BASE(number,2),
n,   LEN(bin),
k,   SEQUENCE(n),
VALUE(MID(bin,k,1))
)``````

Then again, in future it will be possible to use the LAMBDA function to give

where BASEλ is the recursive function

``````= LAMBDA(n,b,
LET(
d,MOD(n,b),
m,QUOTIENT(n,b),
return, IF(m>0,BASEλ(m,b),""),
IF(m>0,VSTACKλ(return,d),d)
)
)``````

and VSTACKλ stacks individual binary digits to form an array

``````= LAMBDA(arr,scalar,
LET(
n, COUNTA(arr),
k, SEQUENCE(n+1),
IF(k<=n, arr, scalar)
)
)``````

Forming a string was easier.  Recursive LAMBDAs are not for the faint-hearted, but they can be useful.

# Re: Working with arrays

Thank you very much for your help everyone