How to convert a signed number to an unsigned number?

Question

Hello,

is there a simple way to convert a signed number to the relative unsigned number, supposing the signed number is usign two's complement?

I'd like to have the following correspondences:

input decimal (binary) || output

-4 (100) || 4

-3 (101) || 5

-2 (110) || 6

-1 (111) || 7

0 (000) || 0

1 (001) || 1

2 (010) || 2

3 (011) || 3

Supposing cell A1 contains the signed decimal number I'm usign the following function:

=2^3*(A1<0)+A1

It works well, but is there a simpler or general way to do the same job avoiding the use of the relation operators "< 0"?

Thanks!

Answer 1

The correct bit conversation would be

=BITAND(A1,2^3-1)

But BITAND does not work for negative numbers, but we can use VBA (if that is an option for you)

Right-click on the sheet tab
Choose "View Code"
Within the menu click Insert \ Module
Paste in the code below
Close the VBA editor
Then apply the formula

=BITAND_VBA(A1,2^3-1)

Andreas.

Function BITAND_VBA(Number, Mask)

BITAND_VBA = Number And Mask
End Function

Answer 2

Hi Alberto,

I think the relation operator <0 is inevitable.

Here is another thought on your requirement: Combine the following Excel inbuilt formula: Bin2Dec & Base

=IF(A1<0,BIN2DEC(BASE(2^3+A1,2)),A1)

Regards,

Alex Chen

Answer 3

Hello Andreas,

in fact I tried first with BITAND but it didn't work.

Thanks a lot to suggest to use BITAND_VBA.

Alberto