Pricing Formula HELP NEEDED

Hi, and welcome to the forum.

It would have assisted if you had uploaded your workbook so that we could understand the layout you're working with but maybe the attached gives you some idea.
Enter stuff in the yellow cells.

I'm sure it is possible, but we have to understand the logic/math we are using to come up with it. Can you explain how you would come up with the price of that 100x100 piece? As a fraction of the total area? As a fraction of width? As a fraction of length? Some other fraction?

area example:
piece: 100x100=10000 mm^2 cost: ??
stock: 610x1000=610000 mm^2 cost: 1.67
proportionality: 10000/610000=??/1.67

width example:
piece: 100 mm cost: ??
stock: 610 mm cost: 1.67
proportionality 100/610=??/1.67

length example:
piece: 100 mm cost: ??
stock: 1000 cost: 1.67
proportionality: 100/1000=??/1.67

You can see that, in each case, I would expect to use a simple proportionality to compute the unkown cost (I assume you remember how to solve proportionalities from early algebra classes). It should just be a matter of deciding exactly how you want to set up the proportionality. If you use the area option, you would also want to consider how you enter the data to make computing areas easiest (it will be easier to enter each dimension as a number into its own cell, rather than have a single cell with the "width x height" text string that you then need to separate out into separate width and height values).

On edit: I note that Richard's response assumed you would be pricing proportionally by area. Note how he used separate cells for the width and height to make area computation easy.

Hello Chris,

The way to do this is create a 2 column table with the Material description in column 1 and the cost in column 2. Name this table say "tbl_MatCost". Also name the cells in the first column of the table say "Materials".

Now with the cell you want as a drop down select the Data option from the Menu and then click the Data Validation item in the Data Tools area then Data Validation.
In the 'Allow' box choose 'List' and enter 'Materials' in the Source box.

Name the drop down cell say "Selection"

Now in the cell that contains the price (C4 in my example) enter the formula

Formula:

Please Login or Register  to view this content.

Hi,

See attached