Results 1 to 7 of 7

20090713, 20:34 #1
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post
I recall where one could identify a group of items from a list by adding the numerical value of each selected item and providing the total as an argument. The individual items were enumerated with the value 2^n where n was unique to each item in the collection. e.g. with item numbers: 0; 1; 2; 3; and 4. If one selected items 1 and 3, the argument would be 10.
My problem is that I cannot recall the technique for extracting the individual values of n which made up the total. How do I determine whether item no 1 with a value of 2 contributed to the argument value of 10?
Help! and Thanks in advance.Regards
Don

20090713, 22:26 #2
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
Since each position is 1 more than the sum all previous positions, you would need to start from the right. Since your total of 10 is less than 2^4 but more than 2^3, you know that the fourth position, or 8, is one of the values. 10  8 leaves you with 2 or 2^1 or second position.
here's a macro I made in Excel that will show the positions for numbers up to 255. The column of numbers must reside in the A column and be selected before running the macro.
Code:Sub FindPosition() Dim cCell As Range Dim cCol As Integer, iFac As Integer Dim i As Integer, lsub As Long For Each cCell In Selection lsub = 0 cCol = 0 For i = 8 To 1 Step 1 If 2 ^ (i  1) <= cCell  lsub Then Cells(cCell.Row, cCol + 2) = i cCol = cCol + 1 lsub = lsub + 2 ^ (i  1) End If Next Next End Sub

20090714, 00:46 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Say the total value is v.
If you want to know whether item n is part of the selection, you can use
Code:If v And 2 ^ n = 0 Then ' not selected Else ' selected End If
Also: 10 And 2 ^ 2 = 0 so item 2 is not selected.
This works because "And" in VBA performs a bitwise And.

20090714, 08:12 #4
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post
[quote name='HansV' post='784450' date='14Jul2009 01:46']Say the total value is v.
If you want to know whether item n is part of the selection, you can use ...[/quote]
Thank you Hans that code strikes a memory bell. However on implementing it I encountered a result which I cannot explain. The attached file contains the following two UDFs.Code:Public Function HansSel(Posn As Long, Sum As Long) As Boolean If 2 ^ Posn And Sum = 0 Then HansSel = False Else HansSel = True End If End Function
Code:Public Function Selected(Posn As Long, Sum As Long) As Boolean Dim Result As Long Result = 2 ^ Posn And Sum If Result = 0 Then Selected = False Else Selected = True End If End Function
Any suggestions on why?Regards
Don

20090714, 08:49 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
If 2 ^ Posn And Sum = 0 Then
is evaluated as
If 2 ^ Posn And (Sum = 0) Then
Since Sum doesn't equal 0, Sum = 0 is False or 0 and hence 2 ^ Posn And (Sum = 0) is False (or 0)
If you insert parentheses
If (2 ^ Posn And Sum) = 0 Then
the HansSel function will have the same result as Selected, where 2 ^ Posn And Sum is evaluated first.

20090714, 10:16 #6
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post
[quote name='HansV' post='784502' date='14Jul2009 09:49']If 2 ^ Posn And Sum = 0 Then
is evaluated as
If 2 ^ Posn And (Sum = 0) Then
Since Sum doesn't equal 0, Sum = 0 is False or 0 and hence 2 ^ Posn And (Sum = 0) is False (or 0)
If you insert parentheses
If (2 ^ Posn And Sum) = 0 Then
the HansSel function will have the same result as Selected, where 2 ^ Posn And Sum is evaluated first.[/quote]
Thank you HansRegards
Don

20090714, 13:28 #7
 Join Date
 Jul 2001
 Location
 Ottawa, Ontario, Canada
 Posts
 1,609
 Thanks
 0
 Thanked 1 Time in 1 Post
[quote name='mbarron' post='784438' date='13Jul2009 23:26']Since each position is 1 more than the sum all previous positions, you would need to start from the right. Since your total of 10 is less than 2^4 but more than 2^3, you know that the fourth position, or 8, is one of the values. 10  8 leaves you with 2 or 2^1 or second position.
here's a macro I made in Excel that will show the positions for numbers up to 255. The column of numbers must reside in the A column and be selected before running the macro.
Code:Sub FindPosition() Dim cCell As Range Dim cCol As Integer, iFac As Integer Dim i As Integer, lsub As Long For Each cCell In Selection lsub = 0 cCol = 0 For i = 8 To 1 Step 1 If 2 ^ (i  1) <= cCell  lsub Then Cells(cCell.Row, cCol + 2) = i cCol = cCol + 1 lsub = lsub + 2 ^ (i  1) End If Next Next End Sub
Thank you Mike.Regards
Don