maques's sparks

És akkor álljon itt az Excel-es (Openoffice-os) XOR implementáció. Mivel az interneten nem találtam hasonló leírást, angolul (is) részletezem, hogy örüljenek más kontinenseken is…

Once upon a time, I wanted to do some basic XOR-ing in excel. Then I realized that there are no such thing a XOR. So I “developed” one and even though this is pretty simple to do, Google doesn’t seem to get such a solution, so I publish it here and now.

Since XOR is basically similar to add, eg.: (binary) adding or XOR-ing 0 vs. 1, 1 vs. 0 and 0 vs. 0 result the same, we only have to “do something” when there are two “1”-s to be dealt, as 1+1=2 but 1 xor 1=0.
Unfortunately to convert to binary, we need to enable Analysis Toolpak in Tools/Add-ins (in hungarian: Eszközök/Bővítménykezelő) to allow to use DEC2BIN (and HEX2BIN, whichever you want) then it is easy pie.

Let’s see 15 XOR 6
15 (decimal) will become 1111 “bin” after a DEC2BIN(15) and 6 will become 110. Adding the converted numbers as decimal will result in 1221. Then a SUBSTITUTE of “2”-s to “0”-s will be 1001, so after a BIN2DEC, you will get the result: 9.
(Doing “OR” instead of “XOR” would need a replacement of “2”-s to “1”-s)

xxx2yyy conversion fuctions have a limit of 0-511, so if your number is above, deal with it first.

So, if A1 and B1 contains the two (decimal) numbers to be xored, then C1 would be:
=SUBSTITUTE(DEC2BIN(A1)+DEC2BIN(B1),2,0)

Magyarul: Ha A1 és B1 tartalmazza a két összexorolandó (decimális) számot, akkor C1:
=HELYETTE(DEC2BIN(A1)+DEC2BIN(B1);2;0)
(angol excel vagy openoffice esetén az angolnál leírt képlet)
Az xxx2xxx függvények használatához engedélyeznünk kell az Eszközök/Bővítménykezelő-ben az Analysis Toolpak bővítményt melyek 0-tól 511-ig terjedő tartományban bírnak (csak) működni.

We get the result in binary, which we can convert with BIN2DEC or BIN2HEX (can also use HEX2BIN instead of DEC2BIN if we want to xor hex numbers right away)

Other ideas welcome / Egyéb ötletek jöhetnek

This entry was posted on Sunday, October 14th, 2007 at 16:47 and is filed under English, Microsoft, technikai infók. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.