I am putting together a program to help me estimate the cost of
various metal parts. One variable is the material cost. The parts are
stamped from a sheet. I know how many parts one sheet will yield
(D25). I know the cost of one sheet (B26). Here's my dilemma; I need
to estimate my material cost based on various quantities. This will
also cause me me to get the material price for additional sheets. The
more sheets I buy the less they cost.
Example; 1 Sheet costs $800 and yields 500 pcs. The quantities I'm
estimating are 100, 250, 500, 1000 and 2000 pcs. This means one sheet
will cover quantity pricing for 100, 250 and 500 pieces and the cost
of material for these quantities is $800 divided by the particular
quantity. I'll need 2 sheets for 1000 pcs and 4 sheets for 2000 pcs. S
sheets cost a total od $1500 and 4 sheets costs $2900. My quantities
a 100 = E27, 250 = F27, 500 = G27, 1000 = h27 and 2000 = I27.
I need a formula that will compute how many sheets a particular
quantity requires and based on that result will utilize the proper
sheet cost. I realize I will need more cells for the different sheets
costs. I will also want the formula to divide the applicable sheet
cost by the applicable quantity to get a cost per piece at that
quantity.Sheets can only be purchased as whole sheets so I cannot buy
3 1/2 sheets, I need instead to buy 4.
I hope all this make sense and i appreciate any help anyone can offer.
Many thanks..

Why have four different cells?

with the Quantity required in E27, in F27 the number of sheets needed will
be returned by the formula:

=CEILING(E27,D25)/D25

The cost of the sheets in G27 can be returned by the formula:

=LOOKUP(F27,{1,2,3,4},B25:B28)

Where the cost of 1,2,3 & 4 sheets are in B25:B28 respectively

Cost per item can be found in Cell H27 by:

G27/E27

or

=ROUNDUP(G27/E27,2)

If you want it rounded up to the nearest cent.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


wrote in message
...
I am putting together a program to help me estimate the cost of
various metal parts. One variable is the material cost. The parts are
stamped from a sheet. I know how many parts one sheet will yield
(D25). I know the cost of one sheet (B26). Here's my dilemma; I need
to estimate my material cost based on various quantities. This will
also cause me me to get the material price for additional sheets. The
more sheets I buy the less they cost.
Example; 1 Sheet costs $800 and yields 500 pcs. The quantities I'm
estimating are 100, 250, 500, 1000 and 2000 pcs. This means one sheet
will cover quantity pricing for 100, 250 and 500 pieces and the cost
of material for these quantities is $800 divided by the particular
quantity. I'll need 2 sheets for 1000 pcs and 4 sheets for 2000 pcs. S
sheets cost a total od $1500 and 4 sheets costs $2900. My quantities
a 100 = E27, 250 = F27, 500 = G27, 1000 = h27 and 2000 = I27.
I need a formula that will compute how many sheets a particular
quantity requires and based on that result will utilize the proper
sheet cost. I realize I will need more cells for the different sheets
costs. I will also want the formula to divide the applicable sheet
cost by the applicable quantity to get a cost per piece at that
quantity.Sheets can only be purchased as whole sheets so I cannot buy
3 1/2 sheets, I need instead to buy 4.
I hope all this make sense and i appreciate any help anyone can offer.
Many thanks..