1

Is it possible to bit-shift a number in a formula in Microsoft Excel?

I want to do something equivalent to

=(a1<<2)

I know I could just divide/multiply by a power of 2 and round, but this would be simpler.

UPDATE: I am using Excel 2010 so I do not have access to BITLSHIFT.

3 Answers 3

1

I don't know the correct syntax but there is a BITLSHIFT and a BITRSHIFT function in Excel.

You can see more about it here https://support.office.com/en-us/article/BITRSHIFT-function-274D6996-F42C-4743-ABDB-4FF95351222C

1
  • See my addition to the question. I am using 2010 so I do not have access to it. Mar 5, 2016 at 19:59
1

E2: =BIN2DEC(RIGHT(TEXT(DEC2BIN(A2),0)&REPT("0",B2),C2))
E3: =RIGHT(MID(A3,3,LEN(A3))&REPT(0,B3),C3)
F2: =BIN2DEC(REPT("0",B2)&MID(DEC2BIN(A2,8),1,LEN(DEC2BIN(A2,8))-B2))
F3: =REPT(0,B3)&MID(A3,1,LEN(A3)-B3)

enter image description here

3
  • That does not work. If I put the number "16" in, I would expect to get 64 but get 61. Mar 5, 2016 at 19:06
  • Oh you're right. I misunderstood and responded in a rush. I was expecting binary input and I did the operation incorrectly anyway. Two strikes. I've updated it.
    – picobit
    Mar 5, 2016 at 19:43
  • I've updated the answer to give the general solution. You can choose right or left, number of places, and the output length.
    – picobit
    Mar 6, 2016 at 17:55
1

we can do the following simple math:

= BINRSHIFT(number, shift_value)

is equivalent to

= number/(2^shift_value)

Similarly,

=BINLSHIFT(number, shift_value)

is equivalent to

= number*(2^shift_value)

 

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .