bitwise functions

I need bitwise functions for a spreadsheet (shift, OR, XOR, etc). Is there a
way to do this in Excel for example? 0  Utf
1/27/2010 3:34:01 PM excel.worksheet.functions  4936 articles. 2 followers. 3 Replies 2796 Views Similar Articles

[PageSpeed] 26

Excel has an OR function and VBA has both OR and XOR but the latter is not
included as a worksheet function but for XOR you can use.

=NOT(A1)+NOT(B1)=1

another XOR

=(A1<>0)+(B1<>0)=1

I have no idea what 'shift' or etc mean in this context
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

"vbano" wrote:

> I need bitwise functions for a spreadsheet (shift, OR, XOR, etc). Is there a
> way to do this in Excel for example? 0  Utf
1/27/2010 5:16:01 PM
"Mike H" wrote:
> Excel has an OR function and [...] for XOR you can use.
> =NOT(A1)+NOT(B1)=1

Excel functions are boolean operations, not bitwise operations.  Compare the
difference between:

=--AND(12,10)

=bitAND(12,10)

Function bitAND(a as long, b as long) as long
bitAND = a AND b
End Function

Presumably, the OP wants the bitAND result.

For Mike's edification, the difference is:  a bitwise function operates on
the individual truth value (0 or 1) of individual bits, whereas a boolean
function operates on the truth value (zero or non-zero) of the entire value.

binAND(12,10) is 8 because 12 is the binary 1100, 10 is the binary 1010, and
8 is the binary 1000.

> I have no idea what 'shift' or etc mean in this context

Ostensibly, shift left and right can be accomplish by multiplying and
dividing by 2, assuming the OP does not want circular shifts.

However, that will not shift bits into and out the sign bit.  I believe that
handling that will also cover the boundary condition the can result in an
overflow error when "shifting left".

----- original message -----

"Mike H" wrote:
> Excel has an OR function and VBA has both OR and XOR but the latter is not
> included as a worksheet function but for XOR you can use.
>
> =NOT(A1)+NOT(B1)=1
>
>  another XOR
>
> =(A1<>0)+(B1<>0)=1
>
> I have no idea what 'shift' or etc mean in this context
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "vbano" wrote:
>
> > I need bitwise functions for a spreadsheet (shift, OR, XOR, etc). Is there a
> > way to do this in Excel for example? 0  Utf
1/27/2010 5:40:09 PM
I wrote:
> Ostensibly, shift left and right can be accomplish by multiplying and
> dividing by 2, assuming the OP does not want circular shifts.
>
> However, that will not shift bits into and out the sign bit.  I believe
> that
> handling that will also cover the boundary condition the can result in an
> overflow error when "shifting left".

I presume that the OP is only interested in bitwise operations for (up to)
32-bit integers -- type Long in VBA.  But of course, Excel will store the
result effectively as type Double.

Ostensibly, that is not a problem since type Double has a 52-bit mantissa
and a separate sign bit.  But ironically, if the left shift is done in Excel
(multiply by 2), there is no overflow problem.

> binAND(12,10) is 8 because 12 is the binary 1100, 10 is the binary 1010,
> and 8 is the binary 1000.

Of course, the type Double representation looks very different.  But that
does not matter because no precision is lost when converting from 32-bit
integer (Long) to Double, then to 32-bit integer again.

----- original message -----

"Joe User" <joeu2004> wrote in message
news:0DC5FFF5-0CE0-4CE9-BA52-CCDB84226067@microsoft.com...
> "Mike H" wrote:
>> Excel has an OR function and [...] for XOR you can use.
>> =NOT(A1)+NOT(B1)=1
>
> Excel functions are boolean operations, not bitwise operations.  Compare
> the
> difference between:
>
> =--AND(12,10)
>
> =bitAND(12,10)
>
> Function bitAND(a as long, b as long) as long
> bitAND = a AND b
> End Function
>
> Presumably, the OP wants the bitAND result.
>
> For Mike's edification, the difference is:  a bitwise function operates on
> the individual truth value (0 or 1) of individual bits, whereas a boolean
> function operates on the truth value (zero or non-zero) of the entire
> value.
>
> binAND(12,10) is 8 because 12 is the binary 1100, 10 is the binary 1010,
> and
> 8 is the binary 1000.
>
>
>> I have no idea what 'shift' or etc mean in this context
>
> Ostensibly, shift left and right can be accomplish by multiplying and
> dividing by 2, assuming the OP does not want circular shifts.
>
> However, that will not shift bits into and out the sign bit.  I believe
> that
> handling that will also cover the boundary condition the can result in an
> overflow error when "shifting left".
>
>
> ----- original message -----
>
> "Mike H" wrote:
>> Excel has an OR function and VBA has both OR and XOR but the latter is
>> not
>> included as a worksheet function but for XOR you can use.
>>
>> =NOT(A1)+NOT(B1)=1
>>
>>  another XOR
>>
>> =(A1<>0)+(B1<>0)=1
>>
>> I have no idea what 'shift' or etc mean in this context
>> --
>> Mike
>>
>> When competing hypotheses are otherwise equal, adopt the hypothesis that
>> introduces the fewest assumptions while still sufficiently answering the
>> question.
>>
>>
>> "vbano" wrote:
>>
>> > I need bitwise functions for a spreadsheet (shift, OR, XOR, etc). Is
>> > there a
>> > way to do this in Excel for example? 0  Joe
1/27/2010 6:00:19 PM Similar Artilces:

Rank Function returns "#N/A"
Basically, I am using the Rank function on a column of cells with value that are numbers. However, in order to make the numbers look the wa that I wanted, I used =TEXT(number,"#,##0"). I'm guessing that RANK does not work on numbers that have had thei format changed by the TEXT() function. Can anyone suggest a way aroun this? THANKS! My first post -- Message posted from http://www.ExcelForum.com Hi, Keep them as numbers and use custom format #,##0 jeff >-----Original Message----- >Basically, I am using the Rank function on a column of cells with values >tha...

Zoom 100% function on my Logitech MX3000 keyboard and mouse not working in e-mail
I just updated my software and driver (4.00.121) for my Logitech MX3000 keyboard and mouse. The 100% Zoom function does not work within an e-mail massage in Outlook 2007. If I open a jpg in a message, I can 'zoom' it smaller and larger but I can not return it to its initail size using the 100% button on the mouse _or_ the keyboard. Using this function in my IE7 browser works just fine. I'm just passing this on. Vista Home Premium \ Office 2007 (12.0.6023.5300) MSO (12.0.6017.5000) Thanks, DH does it work in the other office apps? Logitech drivers and office don't a...

if then function
Hi I am trying to make a formula which does the following if A2 is less than equal to 225 then multiply A2 by cell B2 else if A2 is more than 225 then multiply 225 by cell B2 then subtract 225 from cell A2 and multiply the remainder by cell C2 then add the 2 figures together. i.e. if A2 is 200 then multiply by B2, if A2 is 300 then subtract 225 from 300 (gives 75) multiply 225 by B2 and multiply 75 by cell C2 and add the 2 together. Hope you can understand all this. Thank you in advance Tricia Hi Tricia, How about something like this ... =IF(A2<=225,B2*A2,(A2-225)*C2+(B2*225)) ...

Run a sub every time a function is used in a sheet
I have a custom function in Excel I want to run other sub lets say to display a msg box each time the function is used in the sheet (not each time the function is calculated) ie. only at the time the function is used in the sheet. lets say in coding i want the following Function myfunction (x as variant) as variant myfunction = x+x end function sub mysub () 'this should run each time the function 'my function is used in the cell 'not each time the function is calculated msgbox "Myfunction is used in the active cell" end sub Maybe something like this ...

MIN function
As described in another post of mine, I am calculating totals based on criteria. In this case, I'm using SUMIF to calculate totals using criteria that is between two numbers (>=1 and <=5 for instance). Here is my SUMIF formula: =SUMIF(Revised!\$A\$2:\$A\$336,">="&G2,Revised!\$L\$2:\$L\$336)-SUMIF(Revised!\$A\$2:\$A\$336,">"&I2,Revised!\$L\$2:\$L\$336) What I now want to do is determine the Min, Max, Median, and Average of the cells that fall within the range specified by the parameters. Not sure how to go about doing this... =MIN(IF((Revised!\$A\$2:\$A\$336>...

DB Function?
I have a list of assets in column A. Column B contains a historical date (Jan, 2008) Column C contains current months date (Nov, 2009). Column D shows the period difference (B-C) = 21 Now here comes the fun part, I need to know what the NBV of the asset is using 20% declining balance). This formula will be copied to multiple assets, so it needs to be dynamic. I've tried everything I can think of :S Thanks! cjwenngatz, NBV should be simply the price you paid reduced by the decline multiplied by the number of years that you have had it, limited to the life of the asset ...

Portal 2.0 export to excel non functional
Has anyone else run into issues with BP where the sharepoint base 'export to excel' functionality does not work ? I have talked to MS product support for BP, and Sharepoint and at the end of much 'try this/try that' they have told me to reinstall. Well, thats all well and fine but the users have in the meantime placed significant content on the system and I am not yet comfortable with backup and restoring this system. More worrisome is the fact that attempting to create a test box installed in the exact same fashion as the first to test restoring resulted in a non-fun...

Function to check list for specific conditions and return an answe
I have a list of five cells which may contain any one of the following numbers 017, 030,0. In another cell i want to do the following: If all five cells only contain 017 or 0 then return a value of 017 If all five cells only contain 030 or 0 then return a value of 030 If all five cells contain a combination of all three numbers the cell to show "Check" -- tanya Try this =IF(COUNTIF(A1:A5,"017")+COUNTIF(A1:A5,0)=5,"017",IF(COUNTIF(A1:A5,"030")+COUNTIF(A1:A5,0)=5,"030",IF(COUNTIF(A1:A5,"017")+COUNTIF(A1:A5,"030")+COUNTIF(A...

Function to concatenate cells in a range
Does anyone have an elegant way of concatenating a series of cells? I think the answer will be a user defined function which will be equivalent to MULTICONCAT(start cell : end cell). I think it needs to be a function rather than a macro, so that I can include it in formulae elsewhere in the workbook. I can manage with the limitation of the contents of the cells of a single row or a single column, but it would be nice to have something which was a bit more versatile and could handle a two dimensional array or, best of all, non-contiguous cells. Try this UDF (User Defined func...

How to add functional as the foreign currency on sales invoice lay
Hi, I would like to modify a sales invoice layout by adding two different currencies amount on the layout. The default currency of the company as the foreign currency. For example the default currency of a company is US dollars and I am selling an item in euros. I would like to get both of these currencies amount on the layout. How can I modify this in report writer? Thanks in advance, -- Elaine ...

"IF" Function question
Hello I am trying to create a spreadsheet to keep track of scores. Each score has a corresponding number of points. For example if the score is between 170 and 174.5 the number of points is 1, if it is between 175 and 179.5 it is 2 and so on up to 200 where it is worth 8 points. I can't seem to figure out how to tell excel that if the score falls between two numbers display this value. I have tried it multiple ways such as =IF(D8>=170<=174.5,1, IF(D8>=175<=179.5,2, IF(D8>=180<=184.5,3, IF(D8>=185<=189.5,4, IF(D8>=190<=194.5,4, IF(D8>=195<=197.5,6, IF(D8...

Excel math functions not working
I have a page that displays an Excel table using ASP. Everything seem to work just fine, but any attempt to sum (or run other math functions results in a 0 column total. Ideas? Thanks, Dav -- Message posted from http://www.ExcelForum.com On Thu, 22 Jan 2004 13:58:40 -0600, midgardb <<midgardb.10g8jq@excelforum-nospam.com>> wrote: >I have a page that displays an Excel table using ASP. Everything seems >to work just fine, but any attempt to sum (or run other math functions) >results in a 0 column total. Ideas? > >Thanks, >Dave > > Probably those numbers ...

Help with if function #3
Sorry for another post but I use this =IF(A2<NOW(),"yes","no") How do I add a extra part to it it has to be in between the range of now() and now()+14 How would I put that into the formula Thanks again Greg IF(and(A2>NOW(),A2<NOW()+14), "yes", "no") (you may want to use <= and >=, depending on your exact requirement. Cheers, Christopher "Greg B" <browshop@ihug.com.au> wrote in message news:<d069cj\$ntc\$3@lust.ihug.co.nz>... > Sorry for another post but > > I use this =IF(A2<NOW(),"yes...

Call manaaged C# dll function from unmanaged C++.NET
I have a 3rd party application that can reference external dll's. The dll's have to be written in unmanaged code with an exported function I can reference and call. I would like it to call a C# dll file but it cant call a managed C# dll. I need to create an unmanaged C++.net wrapper for a Managed C# dll. Does anyone know how to do this? can you please give me a simple example of what the C++.Net code would look like to do this Take for example my C# dll has this functio public int ExposedFunction(int X1, int X2 return (X1+X2) What would the C++.Net code look like to call t...

Formula/Function Question
I apologize in advance if there is an obvious solution to this that I am missing: I want to use a formula that will refer to a certain cell that varies from sheet to sheet depending on the value of another cell. I want the cell to refer to cell N"x" N= the column in which to find the value "x"= is a number contained in cell T2 On one sheet, T2 is "6" and then on the next it is "34" If T2 is 6, I would like the cell to reference the data in column N row 6. If T2 is 34, I would like the cell to reference the data in column N row 34. The value of T2 ch...

countif function in multiple worksheets
I have been trying to use the countif function to count the number of times a "Y" response occurs in a specific cell in 15 different worksheets that are all in one workbook. ... I have tried =SUM(COUNTIF('1:15'!B51,{"Y","N"}) but I keep on getting a "value error". This formula works in one worksheet but it wont count multiple worksheets. I've been trying to solve this for days by myself but I'm stuck. I'm a new user and really would appreciate any help. Thank you =SUMPRODUCT(COUNTIF(INDIRECT("'"&...

Calculating Sales Tax with the Paste Function
This may be very basic stuff - but I am stumped. I am trying to use the paste function to calculate sales tax in a spreadsheet. I have the total sales figure in E2 and the tax rate in LA County is 8.25% - I am using the "Percentrank" function and I think the definition of the terms is not what I think it is - this is how far I get: =PERCENTRANK(E2,,8.25%) E2 is the array or field I want to use as the base and then I don't know what "rank" is - and then I think my final number is the percentage rate of 8.25% or they list it as 'significance' - HELP! Alex Tr...

Losing functionality after compiling to mde with MS Acess 2003
Hi Guys, I am new to this forum and I have strange problem with MS Access 2003.. I have a database which is splitted in a front- and a backend. The back-end is located on a network drive. So far nothing new. However I have tested the mde on my own pc and everything works fine. However when I use the same mde on another environment I lose functionality (e.g. A form won't open). When I compile I did not get any error messages. Does anybody have the same experience and maybe a solution? Best Regards, GIP Hi, could be that you have some references in you database, which are not present...

Count Function?
I have a column in a list that contains several numbers, some of which are integers and others which have decimal points. Is there a way to count those cells in a column that contain decimals? One way: =SUMPRODUCT(--(INT(A1:A100)<>A1:A100)) In article <0DF933A0-A0DC-4D96-8C35-A6B7123C47F7@microsoft.com>, jayceejay <jayceejay@discussions.microsoft.com> wrote: > I have a column in a list that contains several numbers, some of which are > integers and others which have decimal points. Is there a way to count those > cells in a column that contain decimals?...

How can I define Erf (the error function) for both negative and p.
How can I make Excel evaluate the error function for both negative and positive numbers? ...

Update value. Function
Hello, I created a simple function on VBA as follows: Function Test() As Integer Dim I As Single For I = 17 To 24 Test = (Test + 1) * Range("Examples_1_a_4!B" & I) Next I End Function On my Excel sheet I have on a cell the following: =Test() It works but when I change a value in the range I need to go to the result cell and click enter to the value be updated. Why? Thanks, Miguel Hi Miguel You have to make you funciton volatile, to do so, add this as first line in you sub: Application.Volatile Regards, Per "shapper" &...

how to perform function (e.g. Sum, Average) on VB 3-d Array
Hi there can I use a excel function such as SUM on a 3d array? e.g. Dim array1(10,10,10) Dim av1 as integer av1 = Application.WorksheetFunction.average(array1(1,1,1):array1(10,1,1) debug.print av1 many thanks If you can't find a function that suits your needs, you can always write your own 3-D sum function as Follows: Option Explicit Option Base 1 Public Sub SumMyArray() Dim intMyArray(2, 3, 4) As Integer Dim lngTheFinalSum As Long intMyArray(1, 1, 1) = 1 intMyArray(1, 1, 2) = 1 intMyArray(1, 1, 3) = 1 intMyArray(1, 1, 4) = 1 intMyArray(1, 2, 1) = 1 intM...

CListCtrl Edit inplace function
Hi. I have 2 CListCtrls. ctrl-a, OnSelChange() changes a global var to equal the newly selected items index. ctrl-b allows users to change the list text using inplace editing, then OnEndLabelEdit() grabs the value of the global var and uses it. The global var is also used elsewhere. Problem is, when im inplace editing in ctrl-b, and i click an item in ctrl-a, OnSelChange() for ctrl-a is called BEFORE OnEndLabelEdit() for ctrl-b so when the OnEnd for ctrl-b is called, the global var is incorrect because it was changed too early! I thought that the OnSelEditEnd message would come a...

FRX
I have two companies but different functional currencies and common reporting currency. Can I use reporting tree in FRX. (V 6.7) for these two companies. ...

Weekday function question
In my excel worksheet I have a column (say Column A)with a series of dates (11/30/0l3, 1/10/04, etc). I would like to be able, say in Column B, to see what day of the week the certain date is. I have looked thru the instructions for the Weekday function, and it is beyond me. Can someone give me a simple formula which I can place into the first cell of Column B and copy all the way down, so that in Column B it will be a weekday corresponding to the date in Column A? To reply by mail, remove 'nick'. Shirley B. Webmaster: http://jumi-shirley-butler.com http://www.geocities.com/mhc_repo...