Excel as a curve and not a step

[rspalding]

Hello,

I have multiple prices in a lookup and I want the pricing returned as a curve and not a step at the different price breaks. Is that possible in Excel?

So prices of 1 @ $50, 10 @ $35, 20 @ $20, 10 @ $5. If my qty is 6, I need it to look at $50 and the $35 and create a curve.

Thanks for the help.

 

[BadgerRoad]

Can you be more specific? Do you want to add a trendline to your data, such as a logarithmic or exponential curve and use that calculated value, or do you want to just interpolate between the two endpoints that surround the value you are using?

 

[Dave Patton]

Your question and requirements are not clear.
Please post examples with expected results with the forum's tool named XL2BB.
You should also edit your profile to show the version of Excel that you use.

 

[rspalding]

Can you be more specific? Do you want to add a trendline to your data, such as a logarithmic or exponential curve and use that calculated value, or do you want to just interpolate between the two endpoints that surround the value you are using?

Yes, I want to just interpolate between the two endpoints that surround the value I am using. But I do have multiple values in the lookup table.

 

[BadgerRoad]

This is probably far more complicated than it has to be, but it seems to work.

Book1
	A	B	C	D	E
1	1	$ 50.00		Quantity:	15
2	10	$ 35.00
3	20	$ 20.00
4	100	$ 5.00		Price:	$ 27.50
5	101	$ -
Sheet1

Cell Formulas
Range	Formula
E4	E4	=OFFSET(INDIRECT(ADDRESS(MATCH(VLOOKUP(E1,A1:B4,2,TRUE),B1:B4,0),2)),0,0)+(E1-OFFSET(INDIRECT(ADDRESS(MATCH(VLOOKUP(E1,A1:B4,2,TRUE),B1:B4,0),1)),0,0))*(OFFSET(INDIRECT(ADDRESS(MATCH(VLOOKUP(E1,A1:B4,2,TRUE),B1:B4,0),2)),1,0)-OFFSET(INDIRECT(ADDRESS(MATCH(VLOOKUP(E1,A1:B4,2,TRUE),B1:B4,0),2)),0,0))/(OFFSET(INDIRECT(ADDRESS(MATCH(VLOOKUP(E1,A1:B4,2,TRUE),B1:B4,0),1)),1,0)-OFFSET(INDIRECT(ADDRESS(MATCH(VLOOKUP(E1,A1:B4,2,TRUE),B1:B4,0),1)),0,0))

 

[Dave Patton]

Your question and requirements are not clear.
Please post examples with expected results with the forum's tool named XL2BB.

If you require the blended rate based on the information that you provided, review the following.

Commissions 2023.xlsm
	A	B	C
11	15	32
12
13	Brackets	Rates	Arithmetic
14
15	0	50	50
16	1	35	350
17	11	20	80
18	31	5
2e

Cell Formulas
Range	Formula
B11	B11	=SUM((A11>{0;1;11;31})*(A11-{0;1;11;31})*({50;-15;-15;-15}))/A11
C15	C15	=MAX(0,MIN($A$11-A15,A16-A15))*B15
C16:C17	C16	=MIN($A$11-A16,A17-A16)*B16