Split a unique number combination?

[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

 

[Pete_UK]

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

 

[Pete_UK]

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,

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- Hide quoted text -

- Show quoted text -

 

[Chip Pearson]

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)

 

[Ron Rosenfeld]

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

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