bracket pricing interpolation

[masterintraining]

50 pc price $55.06
150 pc price $43.71
250 pc price $42.20
500 pc price $39.53

Customer wants to know what order quantity is associated with $40.77 price

I did a scatter plot and the exponential trendline is the best fit although it's not perfect. The bigger problem is with resolution. Quantities are changing pretty fast in the $40.77 area so "eyeballing" the chart is unreliable.
Best way to interpolate quantity at a given price?
Thx-
MinT

[MrShorty]

I don't know if there is a single "best way". This post (http://www.excelforum.com/excel-char...ml#post3904113 ) has an example of one way to do it. If you look at column H:J in my sample workbook, you find a lookup table that regresses parameters for each consecutive pair of points. You can then use a simple lookup function (VLOOKUP() or other -- see formula in column F) to return the m and b for the desired region containing the unkown value, then find y for a given x (or x for a given y). In the sample, the x value to feed into the lookup function is given in column D.

The example uses a log/log function (ln(y)=m*ln(x)+b) as the regression equation. You can change the LINEST() function to get whatever your desired regression would be.

[shg]

If that's your pricing, there's no way he gets $40.77. What is he, a wise guy?

1. You could price based on a linear interpolation between existing prices:

Row\Col	A	B	C
1	Qty	Price
2	50	$ 55.06
3	150	$ 43.71
4	250	$ 42.20
5	500	$ 39.53
6
7		$ 40.77
8	250	$ 42.20	A8: {=INDEX($A$2:$A$5, MATCH(TRUE, $B$2:$B$5>=B7, 1))}
9	500	$ 39.53	A9: {=INDEX($A$2:$A$5, MATCH(TRUE, $B$2:$B$5 < B7, 0))}
10	383.9		A10: =A8 + (B7-B8)/(B9-B8) * (A9-A8)

2. Or, you could use an exponential interpolation between existing prices, but that's more complicated.

3. Or, you could use an exponential pricing model, which gives a differnt price for every quantity.

[Czeslaw]

Please pricing interpolation.

[shg]

Nice, Czeslaw.

Another way:

Row\Col	A	B	C
1	Qty	Price
2	50	$ 55.06
3	150	$ 43.71
4	250	$ 42.20
5	500	$ 39.53
6
7	383.9	$ 40.77	A7: =PERCENTILE($A$2:$A$5, 1-PERCENTRANK($B$2:$B$5, B7, 6))