Formula from Table of Values

[Rekd]

If I have a table of 3 quantities and 3 prices can I extrapolate a formula to calculate any quantity?

For example, I have Part A that I can buy 50 for $5.10, 100 for $4.80 and 150 for $4.10. I want to be able to enter a QTY of say 75 and get the price. Know what I mean?

 

[c_m_s_jr]

Assuming your table looks like:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Qty</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Rate</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>0</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>5.1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>100</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>4.8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>150</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>4.1</TD></TR></TBODY></TABLE>

And you are putting the qty in B2 then in C2 put:
=VLOOKUP(B1,$F$2:$G$4,2,1)

But something tells me your issue is more complex. I would venture to guess you have lots of part numbers with lots of qty thresholds for discounts...

 

[alansidman]

I think that if you are looking for a straight line interpolation, you will have to use the simple algebraic formula for slope and convert it to an excel formula.

http://cs.selu.edu/~rbyrd/math/slope/


Here is how to set it up in Excel
http://www.tutorvista.com/answers/slope-formula-worksheet/103575

Alan

 

[Rekd]

I think that if you are looking for a straight line interpolation, you will have to use the simple algebraic formula for slope and convert it to an excel formula.

http://cs.selu.edu/~rbyrd/math/slope/


Here is how to set it up in Excel
http://www.tutorvista.com/answers/slope-formula-worksheet/103575

Alan

Alan,

Thanks for the link, I will study it as soon as I'm done convincing myself this will probably not be accurate enough for my reports.

You got me to thinking: A straight slope would be easiest, but in reality the parts I'm working with are on more of a bell curve.

Example (Unrelated to previous part costs/qtys)

1 part = $273.11
2 parts = $153.11
5 parts = $81.11
10 parts = $57.11
50 parts = $37.91

The price is based on 3 criteria, one that gets spread across the entire quantity, and two that go to each part regardless of quantity. So without having the price for a QTY of 1 I won't be able to judge the curve. (I know that the more parts there are, the closer I am to the 2 criteria that get added to each part.)