Split a unique number combination?

  • Thread starter Thread starter Peter Noneley
  • Start date Start date
P

Peter Noneley

Hello,

I have a list of options that can be chosen in combination, such as
Small+Pink+Hat.

Each option has been given a unique value.

If I add up the values I get a total that can only have been created
by one combination, such as 2+8+32=42


[My Question]
If I am given a number such as 42, how do I mathematically decode it
into the values, 2 and 8 and 32 ?


Example List of Options
2 Small
4 Large
8 Pink
16 Blue
32 Hat
64 Coat


Thank you
Peter
 
Think of it as a binary number, i.e.:

0 1 0 1 0 1 0

where the first digit represents 64, then 32, 16, 8, 4, 2 and 1, then
it is quite clear where they come from.

Hope this helps.

Pete
 
Further to this, try to subtract the largest power of 2 that you have
(64) - write 1 if you can or 0 if you can't, then move to the next
power of 2 (32), and keep doing this till you have nothing left.

Hope this helps.

Pete

Think of it as a binary number, i.e.:

0 1 0 1 0 1 0

where the first digit represents 64, then 32, 16, 8, 4, 2 and 1, then
it is quite clear where they come from.

Hope this helps.

Pete

Hello,
Click to expand...
I have a list of options that can be chosen in combination, such as
Small+Pink+Hat.
Click to expand...
Each option has been given a unique value.
Click to expand...
If I add up the values I get a total that can only have been created
by one combination, such as 2+8+32=42
Click to expand...
[My Question]
If I am given a number such as 42, how do I mathematically decode it
into the values, 2 and 8 and 32 ?
Click to expand...
Example List of Options
2  Small
4  Large
8  Pink
16 Blue
32 Hat
64 Coat
Click to expand...
Thank you
Peter- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
The key is to use exclusive powers of 2, which will turn specific bits
on or off in a value. An Enum is perfect for this sort of thing. For
example,

Public Enum Color
Red = 0 ^ 2
Blue = 1 ^ 2
Green = 2 ^ 2
End Enum

Public Enum Size
Small = 2 ^ 3
medium = 2 ^ 4
Large = 2 ^ 5
End Enum

Public Enum Thing
Hat = 2 ^ 6
Shoes = 2 ^ 7
Coat = 2 ^ 8
End Enum

Then, you can test is an item is Blue by testing the value against the
Blue value using a bit wise And operation. For example,

Dim IsBlue As Boolean
IsBlue = TheItem And Blue

If TheItem is blue, its Blue bit will be turned on and so the bitwise
comparison TheItem And Blue will return a non-zero value, which is
interpreted as True. Note that since all the bit values in all the
enums are unique, all the bits in a number are 0 except for the single
bit that is turned on by the value in the enum.

You could use a function like the following to get the text
description of an item from its code value:

Function Description(What As Long) As String
Dim S As String

If What And Red Then
S = "Red"
ElseIf What And Blue Then
S = "Blue"
ElseIf What And Green Then
S = "Green"
End If

S = S & " "
If What And Small Then
S = S & "Small"
ElseIf What And medium Then
S = S & "Medium"
ElseIf What And Large Then
S = S & "Large"
End If

S = S & " "
If What And Hat Then
S = S & "Hat"
ElseIf What And Shoes Then
S = S & "Shoes"
ElseIf What And Coat Then
S = S & "Coat"
End If

Description = S

End Function


For example, the code value 161 decodes to "Blue Large Shoes". Work
out the bit values being on or off (1 or 0) and you'll see exactly how
it works.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
The San Diego Project Group, LLC
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)
 
Hello,

I have a list of options that can be chosen in combination, such as
Small+Pink+Hat.

Each option has been given a unique value.

If I add up the values I get a total that can only have been created
by one combination, such as 2+8+32=42


[My Question]
If I am given a number such as 42, how do I mathematically decode it
into the values, 2 and 8 and 32 ?


Example List of Options
2 Small
4 Large
8 Pink
16 Blue
32 Hat
64 Coat


Thank you
Peter
Click to expand...

Here's one approach, and it relies on your numbers being powers of 2.

A2: 2
A3: 4
....
A7: 64

(A2 through A7 could equally well contain the options by Name, as they are only
used as labels).

A2: Small
A3: Large
A4: Pink
A5: Blue
A5: Hat
A7: Coat


B1: Given number between 2-64

B2: =IF(--MID(DEC2BIN($B$1,7),7-ROWS($1:1),1),"X","")

fill down to B7.

This will place an "X" in the appropriate cells corresponding to the options
chosen.
--ron
 
Back
Top