Results 1 to 7 of 7
  1. #1
    Silver Lounger
    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

  2. #2
    5 Star Lounger
    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
    Edited to fix limit to 255 and correct the loop to go to 1 instead of 0

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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
    In your example, 10 And 2 ^ 1 = 2, so item 1 is selected.
    Also: 10 And 2 ^ 2 = 0 so item 2 is not selected.

    This works because "And" in VBA performs a bitwise And.

  4. #4
    Silver Lounger
    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='14-Jul-2009 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
    The HansSel function as called in column F returns TRUE in each case, while the Selection function as called in column B works as expected.

    Any suggestions on why?
    Attached Files Attached Files
    Regards
    Don

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  6. #6
    Silver Lounger
    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='14-Jul-2009 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 Hans
    Regards
    Don

  7. #7
    Silver Lounger
    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='13-Jul-2009 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
    Edited to fix limit to 255 and correct the loop to go to 1 instead of 0[/quote]
    Thank you Mike.
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •