Pricing decreases as quantity increases- how to do that

[rudyeb]

When I purchase raw material, the more I buy the less it costs per
pound. I would like to be able to enter historic data and then have
Excel give me a price that would be in line with that data. Meaning
if
I bought 100 lbs at $3.50/lb and 127 lbs at $3.02/lb what would the
cost be of 185 lbs using those numbers?

 

[Jim Thomlinson]

Depends. In inventory management there is FIFO, LIFO and Average Cost.
Assuming your first purcahse was 100lb and the second 127lb

FIFO First In First Out
100 * 3.50 + 85 * 3.02

LIFO Last In First Out
127 * 3.02 + 58 * 3.50

Average Cost
((100 + 127) / ((100 * 3.50) + (127 * 3.02))) * 185

Check with your accountant as to which one they recommend. Regarless which
one you choose it gets a bit messy in a spreadsheet...

 

[Bob I]

I think the OP was looking for the slope of the line formed by the two
purchases to project a third point.