What Is The Bitshift Left In Excel?

In Excel, a bitshift left is a bitwise operation performed using the BITLSHIFT function, which shifts the binary representation of a number to the left by a specified number of positions. For every position the bits are shifted to the left, a zero (0) is added to the rightmost side of the binary number.

This operation is mathematically equivalent to multiplying the original number by 2 raised to the power of the shift amount.

The BITLSHIFT function

The BITLSHIFT function was introduced in Excel 2013 and has the following syntax:=BITLSHIFT(number, shift_amount)

• number: The non-negative decimal integer you want to shift.
• shift_amount: The number of bit positions to shift the number's binary representation to the left. This must be an integer.

How it works: A step-by-step example

Let's use the BITLSHIFT function with an example to see how the bitshifting process works.

Scenario: Shift the decimal number 4 to the left by 2 bits.The formula would be: =BITLSHIFT(4, 2)

1. Convert to binary: Excel first converts the decimal number 4 into its binary equivalent, which is 100.
2. Shift the bits: The function then shifts every bit in 100 two positions to the left, which creates two open slots on the right side.
3. Fill with zeros: The function fills the empty slots with two zeros, resulting in the new binary number 10000.
4. Convert back to decimal: Finally, Excel converts the binary result 10000 back into its decimal form, which is 16.

The mathematical equivalent

For those who are not familiar with bitwise operations, the same result can be achieved with a simple mathematical formula. A left bitshift is equivalent to multiplying the original number by 2 raised to the power of the shift_amount.

Using the same example:

• Original number = 4
• Shift amount = 2
• Formula: 4 * (2^2) = 4 * 4 = 16.

This shows that the BITLSHIFT function is a shortcut for a more complex multiplication operation, making it useful for specific data-handling and technical calculations.

Common errors and constraints

It is important to be aware of the function's limitations to avoid formula errors.

• #NUM! error: This occurs if either the number or the shift_amount is outside of the function's constraints.
  • The number must be between 0 and (2^48)-1.
  • The absolute value of shift_amount must be less than or equal to 53.
  • If the result of the bitshift calculation exceeds the maximum limit, the function will also return a #NUM! error.
• #VALUE! error: This error appears if either of the arguments are non-numeric.
• Negative shift_amount: If you provide a negative shift_amount, the function will perform a right bitshift instead. A right bitshift is also available as a separate function: BITRSHIFT.

Practical applications

While bitwise operations may seem obscure for general spreadsheet tasks, they are essential for certain technical and data-driven applications.

• Data encoding and decoding: Programmers and engineers use bitwise shifts to manipulate binary data for efficient encoding and decoding.
• Permissions and flags: In technical data, specific bits can represent a list of true/false flags or permissions. Bitwise shifts can be used to set, check, or retrieve the status of a specific flag.
• Algorithmic calculations: When working with specialized algorithms, left bitshifts can be a more efficient way to multiply numbers by powers of two, as it's a faster operation at the processor level.