Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Bitwise Functions?

841 views
Skip to first unread message

John Avitt

unread,
Sep 6, 2002, 3:29:41 PM9/6/02
to
I want to do bitwise comparisons in Excel *without* using VBA.
This is simple with VBA, but I want to do it without VBA, so
users won't see the disable/enable macros dialog on open.

Thank you.

John

Harlan Grove

unread,
Sep 6, 2002, 7:53:23 PM9/6/02
to
John Avitt <john...@spamless.hotmail.com> wrote...

>I want to do bitwise comparisons in Excel *without* using VBA.
>This is simple with VBA, but I want to do it without VBA, so
>users won't see the disable/enable macros dialog on open.

If you must, if x and y are integer values, then

NOT x:
=SUMPRODUCT(1-MOD(INT(x/Bits),2),Bits)

x AND y:
=SUMPRODUCT(MOD(INT(x/Bits),2)*MOD(INT(x/Bits),2),Bits)

x OR y:
=SUMPRODUCT(SIGN(MOD(INT(x/Bits),2)+MOD(INT(y/Bits),2)),Bits)

x XOR y:
=SUMPRODUCT(MOD(MOD(INT(x/Bits),2)+MOD(INT(x/Bits),2),2),Bits)

x IMP y:
=SUMPRODUCT(1-(MOD(INT(x/Bits),2)*(1-MOD(INT(y/Bits),2))),Bits)

x EQ y:
=SUMPRODUCT(1-MOD(MOD(INT(x/Bits),2)+MOD(INT(y/Bits),2),2),Bits)


where Bits is a defined name like any of the following.

8-bits =2^(16-ROW(INDIRECT("1:8")))

16-bits =2^(16-ROW(INDIRECT("1:16")))

32-bits =2^(16-ROW(INDIRECT("1:32")))

You're on your own for character and noninteger numeric bit bashing.


David J. Braden

unread,
Sep 6, 2002, 8:39:54 PM9/6/02
to
Harlan,
You have outdone yourself. I sit in awe.

Regards,
Dave Braden

In article
<Tdbe9.14846$jG2.1...@bgtnsc05-news.ops.worldnet.att.net>, Harlan
Grove <Hrl...@aol.com> wrote:

--
E-mail (ROT-13'ed): qoe...@ebpurfgre.ee.pbz

John Walkenbach

unread,
Sep 6, 2002, 9:34:53 PM9/6/02
to
<AOL>
Me too!
</AOL>

-John

"David J. Braden" <r_u_k...@me.noway.invalid> wrote in message
news:060920022039545621%r_u_k...@me.noway.invalid...

Stephen Dunn

unread,
Sep 9, 2002, 4:18:42 PM9/9/02
to
Hi Harlan,

a little too much copy and paste in there I think, take a fresh look at
x AND y.

Also, shouldn't the named formulae be:

8-bits =2^(8-ROW(INDIRECT("1:8")))
32-bits =2^(32-ROW(INDIRECT("1:32")))

?

Steve D.
<otherwise impressed>


"Harlan Grove" <Hrl...@aol.com> wrote in message
news:Tdbe9.14846$jG2.1...@bgtnsc05-news.ops.worldnet.att.net...

Harlan Grove

unread,
Sep 9, 2002, 7:15:01 PM9/9/02
to
"Stephen Dunn" <st...@btinternet.com> wrote...
...

>a little too much copy and paste in there I think, take a fresh look at
>x AND y.

<corrected>
x AND y:
=SUMPRODUCT(MOD(INT(x/Bits),2)*MOD(INT(y/Bits),2),Bits)

> Also, shouldn't the named formulae be:
>
> 8-bits =2^(8-ROW(INDIRECT("1:8")))
> 32-bits =2^(32-ROW(INDIRECT("1:32")))

Good catches.


John Avitt

unread,
Sep 10, 2002, 9:38:21 AM9/10/02
to
Thank you.


nema....@gmail.com

unread,
Nov 19, 2012, 3:33:17 AM11/19/12
to John Avitt
Пʼятниця, 6 вересня 2002 р. 22:40:07 UTC+3 користувач John Avitt написав:
in 2007 (transp view ;)

IP-Address 4.69.17.254
Subnet Mask 255.255.255.248
addr_dot_1 =FIND(".";Table4[[#This row];[IP-Address]];1)
mask_dot_1 =FIND(".";Table4[[#This row];[Subnet Mask]];1)
addr_dot_2 =FIND(".";Table4[[#This row];[IP-Address]];Table4[[#This row];[addr_dot_1]]+1)
mask_dot_2 =FIND(".";Table4[[#This row];[Subnet Mask]];Table4[[#This row];[mask_dot_1]]+1)
addr_dot_3 =FIND(".";Table4[[#This row];[IP-Address]];Table4[[#This row];[addr_dot_2]]+1)
mask_dot_3 =FIND(".";Table4[[#This row];[Subnet Mask]];Table4[[#This row];[mask_dot_2]]+1)
addr_first =MID(Table4[[#This row];[IP-Address]];1;Table4[[#This row];[addr_dot_1]]-1)
mask_first =MID(Table4[[#This row];[Subnet Mask]];1;Table4[[#This row];[mask_dot_1]]-1)
addr_second =MID(Table4[[#This row];[IP-Address]];Table4[[#This row];[addr_dot_1]]+1;Table4[[#This row];[addr_dot_2]]-Table4[[#This row];[addr_dot_1]]-1)
mask_second =MID(Table4[[#This row];[Subnet Mask]];Table4[[#This row];[mask_dot_1]]+1;Table4[[#This row];[mask_dot_2]]-Table4[[#This row];[mask_dot_1]]-1)
addr_third =MID(Table4[[#This row];[IP-Address]];Table4[[#This row];[addr_dot_2]]+1;Table4[[#This row];[addr_dot_3]]-Table4[[#This row];[addr_dot_2]]-1)
mask_third =MID(Table4[[#This row];[Subnet Mask]];Table4[[#This row];[mask_dot_2]]+1;Table4[[#This row];[mask_dot_3]]-Table4[[#This row];[mask_dot_2]]-1)
addr_fourth =MID(Table4[[#This row];[IP-Address]];Table4[[#This row];[addr_dot_3]]+1;LEN(Table4[[#This row];[IP-Address]])-Table4[[#This row];[addr_dot_3]])
mask_fourth =MID(Table4[[#This row];[Subnet Mask]];Table4[[#This row];[mask_dot_3]]+1;LEN(Table4[[#This row];[Subnet Mask]])-Table4[[#This row];[mask_dot_3]])
addr_first2 =DEC2BIN(Table4[[#This row];[addr_first]];8)
mask_first3 =DEC2BIN(Table4[[#This row];[mask_first]];8)
addr_second4 =DEC2BIN(Table4[[#This row];[addr_second]];8)
mask_second5 =DEC2BIN(Table4[[#This row];[mask_second]];8)
addr_third6 =DEC2BIN(Table4[[#This row];[addr_third]];8)
mask_third7 =DEC2BIN(Table4[[#This row];[mask_third]];8)
addr_fourth8 =DEC2BIN(Table4[[#This row];[addr_fourth]];8)
mask_fourth9 =DEC2BIN(Table4[[#This row];[mask_fourth]];8)
addr_bin_concatenate =CONCATENATE(Table4[[#This row];[addr_first2]];Table4[[#This row];[addr_second4]];Table4[[#This row];[addr_third6]];Table4[[#This row];[addr_fourth8]])
mask_bin_concatenate =CONCATENATE(Table4[[#This row];[mask_first3]];Table4[[#This row];[mask_second5]];Table4[[#This row];[mask_third7]];Table4[[#This row];[mask_fourth9]])
0_pos_in_mask =FIND(0;Table4[[#This row];[mask_bin_concatenate]];1)
network_bin_arrd_part =LEFT(Table4[[#This row];[addr_bin_concatenate]];Table4[[#This row];[0_pos_in_mask]]-1)
network_bin_mask_part =RIGHT(Table4[[#This row];[mask_bin_concatenate]];33-Table4[[#This row];[0_pos_in_mask]])
network_bin_ =CONCATENATE(Table4[[#This row];[network_bin_arrd_part]];Table4[[#This row];[network_bin_mask_part]])
network_len =LEN(Table4[[#This row];[network_bin_]])
network_bin_1 =MID(Table4[[#This row];[network_bin_]];1;8)
network_bin_2 =MID(Table4[[#This row];[network_bin_]];9;8)
network_bin_3 =MID(Table4[[#This row];[network_bin_]];17;8)
network_bin_4 =MID(Table4[[#This row];[network_bin_]];25;32)
network_dec_1 =BIN2DEC(Table4[[#This row];[network_bin_1]])
network_dec_2 =BIN2DEC(Table4[[#This row];[network_bin_2]])
network_dec_3 =BIN2DEC(Table4[[#This row];[network_bin_3]])
network_dec_4 =BIN2DEC(Table4[[#This row];[network_bin_4]])
network_address =CONCATENATE(Table4[[#This row];[network_dec_1]];".";Table4[[#This row];[network_dec_2]];".";Table4[[#This row];[network_dec_3]];".";Table4[[#This row];[network_dec_4]])
0 new messages