Working with arrays

%3CLINGO-SUB%20id%3D%22lingo-sub-2483531%22%20slang%3D%22en-US%22%3EWorking%20with%20arrays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2483531%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%20guys%20I%C2%B4m%20not%20the%20best%20in%20using%20excel%20files%2C%20I%20would%20really%20apriciate%20some%20help%20with%20my%20university%20task.%20Thanks%20a%20lot%20!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CU%3ETitle%3A%20Arrays%3C%2FU%3E%3C%2FP%3E%3CP%3ERead%20any%20integer%20from%20the%20worksheet%20and%20convert%20it%20to%20a%20binary%20value%20to%20be%20stored%20in%20the%20array.%20Example%3A%3C%2FP%3E%3CP%3ENumber%20%3D%2010%3C%2FP%3E%3CP%3EBinary%20representation%20%3D%20%5B1%2C0%2C1%2C0%5D%3C%2FP%3E%3CP%3EThe%20algorithm%20for%20converting%20to%20binary%20is%20as%20follows%3A%3C%2FP%3E%3CUL%3E%3CLI%3EStep%201%3A%20Divide%20the%20number%20by%202%20through%20%25%20(modulus%20operator)%20and%20store%20the%20remainder%20in%20array.%3C%2FLI%3E%3CLI%3EStep%202%3A%20Divide%20the%20number%20by%202%20through%20%2F%20(division%20operator)%3C%2FLI%3E%3CLI%3EStep%203%3A%20Repeat%20the%20step%202%20until%20number%20is%20greater%20than%200.%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2483531%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2483720%22%20slang%3D%22en-US%22%3ERe%3A%20Working%20with%20arrays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2483720%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1087519%22%20target%3D%22_blank%22%3E%40novys8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20may%20not%20be%20the%20best%20way%20but%20it%20divides%20by%20two%20and%20counts%20remainders%20until%20there%20is%20nothing%20left.%20N%20divides%20by%20two%2C%20M%20carries%20that%20down%2C%20and%20O%20looks%20if%20its%20even%20(there%20will%20be%20no%20remainder%20so%20set%20to%200%2C%20otherwise%20print%201).%20The%20concat%20strings%20the%20binary%20digits%20together%2C%20and%20the%20result%20formula%20reverses%20the%20concat%20result%20to%20get%20the%20final%20binary%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DKoontz_1-1624576374325.png%22%20style%3D%22width%3A%20461px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291155i9462CA893CD2DBE3%2Fimage-dimensions%2F461x224%3Fv%3Dv2%22%20width%3D%22461%22%20height%3D%22224%22%20role%3D%22button%22%20title%3D%22DKoontz_1-1624576374325.png%22%20alt%3D%22DKoontz_1-1624576374325.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20formula%20view%2C%20a%20bity%20messy%20but%20it%20works!%20It%20goes%20past%20the%20512%20limit%20set%20by%20excel%20%3DDEC2BIN%20function.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DKoontz_0-1624576185015.png%22%20style%3D%22width%3A%20767px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291154i7AB96B628446F4F3%2Fimage-dimensions%2F767x205%3Fv%3Dv2%22%20width%3D%22767%22%20height%3D%22205%22%20role%3D%22button%22%20title%3D%22DKoontz_0-1624576185015.png%22%20alt%3D%22DKoontz_0-1624576185015.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EM%3A%26nbsp%3B%3D%2BN52%3C%2FP%3E%3CP%3EN%3A%26nbsp%3B%3D%2BROUNDDOWN(M53%2F2%2C0)%3C%2FP%3E%3CP%3EO%3A%26nbsp%3B%3D%2BIF(M53%3D0%2C%22%22%2CIF(ISEVEN(M53)%3DTRUE%2C0%2C1))%3C%2FP%3E%3CP%3EConcat%3A%26nbsp%3B%3D%2BCONCAT(O52%3AO65)%3C%2FP%3E%3CP%3EResult%3A%26nbsp%3B%3DTEXTJOIN(%22%22%2C1%2CMID(R52%2CABS(ROW(INDIRECT(%221%3A%22%26amp%3BLEN(R52)))-(LEN(R52)%2B1))%2C1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2485167%22%20slang%3D%22en-US%22%3ERe%3A%20Working%20with%20arrays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2485167%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1087519%22%20target%3D%22_blank%22%3E%40novys8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20395px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291275i32611BE3C7D9500D%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2486129%22%20slang%3D%22en-US%22%3ERe%3A%20Working%20with%20arrays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2486129%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1087519%22%20target%3D%22_blank%22%3E%40novys8%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20need%20to%20clarify%20whether%20you're%20looking%20for%20a%20formula%20solution%20or%20a%20vba%20solution%3F%20I%20believe%20%22%25%22%20is%20a%20modulo%20operator%20in%20some%20other%20programming%20languages%2C%20but%20I'm%20not%20aware%20of%20it%20being%20the%20used%20for%20such%20in%20Excel%2FVBA.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you're%20looking%20at%20vba%2C%20then%20perhaps%20something%20like%20this%20(but%20instead%20of%20using%20the%20division%20operator%2C%20I'm%20using%20the%20integer%20division%20operator%20%22%5C%22%20-%20I%20don't%20think%20it%20will%20work%20correctly%20with%20just%20%22%2F%22%20operator%20unless%20you%20wrap%20it%20with%20the%20Int%20function)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPublic%20Function%20DecToBinary(ByVal%20decNum%20As%20Long)%20As%20Variant%0A%20%20%20%20%20Dim%20binNum()%20As%20String%3A%20ReDim%20binNum(0)%0A%20%20%20%20%20Dim%20intPart%20As%20Integer%3A%20intPart%20%3D%20decNum%0A%20%20%20%20%20Dim%20modPart%20As%20Integer%0A%20%20%20%20%20%0A%20%20%20%20%20Do%0A%20%20%20%20%20%20%20%20%20%20modPart%20%3D%20intPart%20Mod%202%0A%20%20%20%20%20%20%20%20%20%20intPart%20%3D%20intPart%20%5C%202%0A%20%20%20%20%20%20%20%20%20%20binNum(UBound(binNum))%20%3D%20modPart%0A%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20If%20intPart%20%26gt%3B%200%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ReDim%20Preserve%20binNum(UBound(binNum)%20%2B%201)%0A%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20DoEvents%0A%20%20%20%20%20Loop%20Until%20intPart%20%3D%200%0A%20%20%20%20%20%0A%20%20%20%20%20DecToBinary%20%3D%20Split(StrReverse(Join(binNum%2C%20%22%3B%22))%2C%20%22%3B%22)%0A%20%20%20%20%20%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@novys8 

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.

 

DKoontz_1-1624576374325.png

 

 

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

DKoontz_0-1624576185015.png

 

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))

 

@novys8 

That could be like this

image.png

@novys8 

 

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

@novys8 

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

image.png

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.

 

Thank you very much for your help everyone