Linear sliding scale price increase?

[marcopolo12345]

Hey everyone

What formula could I use to create a markup/price increase for the 100 000 items in my online store where markup decreases as prices increase:
if buying price is <= $4, increase selling price x2
if buying price is > than $15 increase selling price x1.6
the markup for buying prices between $4 and $15 should gradually decrease from x2 to x1.6 in a linear way (not stepped).

I know there are a few formulas of this kind in this forum, but I couldn't find anything that I could use to fit this requirement.

thank you

[shg]

Welcome to the board.

Row\Col	A	B	C
1	Cost	Price
2	$ 1.00	$ 2.00	B2: =MIN(2, MAX(1.6, 2 - (2 - 1.6) * (A2-4) / (15-4))) * A2
3	$ 2.00	$ 4.00
4	$ 3.00	$ 6.00
5	$ 4.00	$ 8.00
6	$ 5.00	$ 9.82
7	$ 6.00	$ 11.56
8	$ 7.00	$ 13.24
9	$ 8.00	$ 14.84
10	$ 9.00	$ 16.36
11	$ 10.00	$ 17.82
12	$ 11.00	$ 19.20
13	$ 12.00	$ 20.51
14	$ 13.00	$ 21.75
15	$ 14.00	$ 22.91
16	$ 15.00	$ 24.00
17	$ 16.00	$ 25.60
18	$ 17.00	$ 27.20
19	$ 18.00	$ 28.80
20	$ 19.00	$ 30.40
21	$ 20.00	$ 32.00

[shg]

That's

=MIN(maxMult, MAX(minMult, maxMult - (maxMult - minMult) * (cost - costMin) / (costMax - costMin))) * cost

[marcopolo12345]

Amazing
thank you!!

[shg]

You're welcome.

[marcopolo12345]

Hi shg

do you remember coming up with this formula?
I've just tried to modify it a little to make some adjustments and noticed that it's not working if you deviate too much from these numbers and I can't figure out why.
Example, if you change it to
=MIN(5, MAX(2, 5 - (5 - 2) * (A2-3) / (10-3))) * A2
then the prices don't increase linearly; after $7 the new prices go down and then up again after $12

Does that make sense?

[shg]

?

	A	B	C
1	maxMult	minMult
2	5	2
3	minCost	maxCost
4	$ 3.00	$ 10.00
5
6	Cost	Price
7	$ 1.00	$ 5.00	B7: =MIN(maxMult, MAX(minMult, maxMult - (maxMult - minMult) * (cost - minCost) / (maxCost - minCost))) * cost
8	$ 2.00	$ 10.00
9	$ 3.00	$ 15.00
10	$ 4.00	$ 18.29
11	$ 5.00	$ 20.71
12	$ 6.00	$ 22.29
13	$ 7.00	$ 23.00
14	$ 8.00	$ 22.86
15	$ 9.00	$ 21.86
16	$ 10.00	$ 20.00
17	$ 11.00	$ 22.00
18	$ 12.00	$ 24.00

[ben_hensel]

Price is not a linear function of Cost. I mean, inside the bounded range -- it's something like

Price = Cost * Multiplier

Where Multiplier is actually a linear function of Cost, call it Cost * Coefficient

Price = Cost * Cost * Coefficient = Cost^2 * Coefficient

So that Coefficient is linear, but the Price is quadratic (it's actually more like Price = Cost^2 + A*Cost + B but that's not terribly germane to the point I'm making)

So what you've got is a local maxima at Cost =7, because the margin is decreasing faster than the cost is increasing.

See the attached chart to illustrate.

[marcopolo12345]

That's great help, thank you.

Is there a way to make it linear inside the bounded range?

[shg]

	A	B
1	maxMult	minMult
2	5	2
3	minCost	maxCost
4	$3.00	$10.00
5
6	Cost	Price
7	$1.00	$5.00
8	$2.00	$10.00
9	$3.00	$15.00
10	$4.00	$15.71
11	$5.00	$16.43
12	$6.00	$17.14
13	$7.00	$17.86
14	$8.00	$18.57
15	$9.00	$19.29
16	$10.00	$20.00
17	$11.00	$22.00
18	$12.00	$24.00

In B7:

Please Login or Register  to view this content.

[marcopolo12345]

looks like this is working
thank you shg