CODEWITHSUNDEEP
excelexcel-formula
EvilDr
EvilDr

Reputation: 9610

How to generate a curved price reduction rather than stepped

I have a spreadsheet that calculates a price based on the number of units purchased. The more units purchased; the cheaper the price-per-unit.

There is an issue in my approach however whereby:

Total price for 100 units = £2,500.00

Total price for 101 units = £2,020.00

The price has reduced as the item quantity increases across the pricing step. I've tried more pricing steps with lower reductions, but the problem still exists but on a smaller scale.

enter image description here

This clearly isn't funding my early retirement plans. So I was wondering if anyone can recommend a formula for a curved pricing structure, rather than stepped. I need the price-per-unit to fall as the number of units increases, but not so as for the total price to ever drop as the units rise.

The spreadsheet is here.

Upvotes: 0

Views: 274

Answers (2)

essbee
essbee

Reputation: 317

You can convert price into a formula that varies it inversely with quantity.

I have used the following formula in sample_data_set: price = 1/quantity - 1/(quantity ^ 2)

Using this function, the total value converges to 1.

Finally, you can multiply the entire formula with a constant value, c, to increase the value > 1.

So, if the desired price for 2 units is $500, set c = $1000.

EDIT: IF you would like a smoother (more gentle) gradient, you can add more exponential functions, as follows: price = constant * [ (1/qty) - (1/qty^2) - (1/qty^3) ]

Upvotes: 1

MikeC
MikeC

Reputation: 958

For any single purchase, you could offer the rate for the applicable tiers and charge standard price for remaining.

Example 1: Purchase of 10 items. Price is 30 pounds for 10 items.

Example 2: Purchase of 60 items. Price is 30 pounds for 50 items. Price is 30 pounds for 10 items. (You're not offering any discount at 50 items tier)

Example 3: Purchase of 110 items. Price is 25 pounds for 100 items. Price is 30 pounds for 10 items.

Example 4: Purchase of 260 items. Price is 20 pounds for 100 items. Price is 30 pounds for 60 items.

Example 5: Purchase of 1790 items. Price is 10 pounds for 1000 items. Price is 15 pounds for 500 items. Price is 20 pounds for 200 items. Price is 30 pounds for 90 items.

Having said that, the price points are dropping a little sharply in your data, for real life. I also predict this question will be closed or put on hold. :-)

Upvotes: 1

Related Questions