Assigning a variable

[Zack]

How do I go about assigning a number to a particular piece of data? To give a
simple example of what I'm trying to do, and what I envision, I'll pretend
I'm building a sandwich.

1=white
2=wheat
3=ham
4=turkey
5=roast beef
6=lettuce
7=tomato
8=mayo
9=mustard
10=cheese

So, someone could come along and build their sandwich,
ham/turkey/tomato/mayo on white bread. Excel would then recognize that
1+3+4+7+8=23
23=The American (the name of the sandwich, which I've already assigned a
variable to)

I'm using the sandwich model because its a lot simpler than what I'm
attempting to do.

 

[Pete_UK]

The problem with that simple model is that there could be other ways
of forming the number 23. An alternative numbering system would be to
consider the number as a series of 0s and 1s where each digit
represents one of the choices in sequence (starting with cheese). So,
your sandwich would be:

0 0 1 1 0 0 1 1 0 1

Now, you can actually think of this as being a binary number, which
has an equivalent decimal value of 205. This is unique for that
combination of choices. So, the numbers for each choice would be:

1=white
2=wheat
4=ham
8=turkey
16=roast beef
32=lettuce
64=tomato
128=mayo
256=mustard
512=cheese

You can extend this as far as you need.

Is this something you can work with (or am I barking up the wrong
tree?) ?

Hope this helps.

Pete

 

[Zack]

First, assigning a variable probably isn't the most accurate statement. I'm
really looking to assign a value to a particular piece of data. Second,
you've hit upon a critical element to this with regard to the numbering
sequence. Fortunately, I'm not building a sandwich, I'm building geothermal
units and there's about 500 different configurations a customer can choose
from.

What's the best way of going about assigning a value to each option?

 

[Pete_UK]

Well, if an option is really a matter of being included or not
included, then a binary approach as I outlined above would be
appropriate.

However, I suspect you might have some options that are multiple-
choices, like colour - here you could have a coding system like R for
red, B for Black etc, but this simple approach falls down if you offer
Blue as an option. Though you could still adopt a binary approach for
this, you would need several "bits" to describe the colour and the
numbers would not be so obvious as to what they represented.

Obviously, I know nothing about the options you offer, so I can only
generalise unless you can be more specific.

Hope this helps.

Pete