how to apply bitand on multiple ranges in excel

user_1285419 0
Viewed 101

I have an Excel spreadsheet with 3 columns, where each column contains some random numbers. I am going to find out how many numbers in the first column and second column have a non-zero output from BITAND.

For example, assuming two columns A, B

A    B
1    0
2    64
4    8
8    7
16
  • 0 and 64 in B have no single bit "1" in common with any number in A.
  • Number 8 in B have common bit 1 with the 8 in A.
  • Number 7 in B have common bit 1 with number 1, 2, 4 in A.

Therefore, there are total of 4 pairs of numbers with non-zero BITAND result.

I find that Excel's BITAND does not support operation on a range of cells and therefore I am using a very inefficient method to do the work. Assuming the first column has 100 numbers and the second column has 55 numbers.

  1. I first fix one number in the second column and have each number in the first column BITAND with that number, which will generate a new column of 100 numbers.
  2. Repeat the same procedure with the 1st column and the next number in the second column until all numbers in the second number looped.
  3. That will generate a total of 55 additional columns each have 100 results of BITAND.
  4. I then count the total number of non-zero entries.

I wonder if there is any better method instead of macro that will help to reduce the work since I may have more than 200 numbers per column in the future.

PS a sample Excel with my solution could be downloaded at enter link description here

excel-formula excel
1 Answers

Using formulas, you could try a simple grid where b is transposed over the columns. Each grid cell has formula =BITAND(x,y) where x and y are references to D4:D8 and E3:H3.

In cells E2 put =COUNTIF(E4:E8,">0") which you can drag across to the right.

Then, in cell E1 put =SUM(E2:H2) to get the desired output of 4

enter image description here

You can accommodate new entries to a and b and extend the matrix without breaking the existing result (as BITAND(0,0) = 0):

enter image description here

I've used 14 extra rows in a and 7 extra rows for b, but this can be any number you like - the output number will still end up in cell E1. To semi-automate the transpose

  • in row 3 you can use =INDEX($B$2:$B$12,COLUMN()-4) where 4 is constant in reference to column D
  • in column D the formula is simply =A2 and drag down

enter image description here

If you know the upper limit on the number of rows you expect in the future, you can set it up for that and let it grow over time simply by adding new values to a and b.

user_7653950
Related