Jun 24 2021 02:25 PM
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:
Jun 24 2021 04:14 PM
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))
Jun 25 2021 09:06 AM
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
Jun 25 2021 01:30 PM
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.