assigning numbers to text entries

[greengrass]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hello.

I am a novice Excel user trying to create a database of a product line and all its many variables. I have input the information in text only format but now need to assign a monetary value to certain words so I can create a formula which will give me a price for each variant of the product. So for example i would like "spiral" to equal £60 throughout the entire worksheet and "rectangle" £40. There are tens of thousand of possible variations to the final product so manually putting prices in isn't viable. These amounts will change over time too, so ideally they will be added in a way which can be simply amended.

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hello.

I am a novice Excel user trying to create a database of a product line and all
its many variables. I have input the information in text only format but now
need to assign a monetary value to certain words so I can create a formula
which will give me a price for each variant of the product. So for example i
would like "spiral" to equal £60 throughout the entire worksheet and
"rectangle" £40. There are tens of thousand of possible variations to the
final product so manually putting prices in isn't viable. These amounts will
change over time too, so ideally they will be added in a way which can be
simply amended.

I'm not entirely sure what you want, but try this and let us know if that
helps of if you need anything else. I assume the text descriptions are in a
single cell, so first, duplicate your text into another column. In that
column, do a find and replace. For example, find "Spiral" and replace it
with "+60", then do it again, replacing "rectangle" with "+40". Repeat until
all the text has been replaced with values.

 

[Laroche J]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hello.

I am a novice Excel user trying to create a database of a product line and all
its many variables. I have input the information in text only format but now
need to assign a monetary value to certain words so I can create a formula
which will give me a price for each variant of the product. So for example i
would like "spiral" to equal £60 throughout the entire worksheet and
"rectangle" £40. There are tens of thousand of possible variations to the
final product so manually putting prices in isn't viable. These amounts will
change over time too, so ideally they will be added in a way which can be
simply amended.

Like Bob I'm not entirely sure what you want, but my take is that you may
have a product variant with 3 spirals, another one with one spiral and 4
rectangles, etc., and you want an easy way to calculate the price of each
variant.

For the following I'd like to know how your database is set up. For example,
let's say the variant identification uses column A, each possible part
making them up fills columns B to F, and finally column G is the price. What
you could do in this instance is use the second row (below the part name) to
enter the price of that part, and enter in the cells intersecting the
product variant and the part name the quantity of that part for that
variant. The price cell (G3, for instance) for that variant would then be
calculated by =SUMPRODUCT($B$2:$F$2;B3:F3)

If neither Bob nor I got it right, describe your database more precisely.

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 12.1.7