I'm having trouble matching the answer to my formula to the correct total.
If a customer buys, a large quantity of product, I want to offer them tiered pricing instead of average pricing. The problem with selling them ample units using the average pricing model is that the price per unit goes down so quickly that eventually it ends up costing the customer less in total to buy more product. Eventually my discounted rate goes down so low, I'm giving it away!
Instead, the customer should pay $5 for 100 units, plus $4.90 for the next hundred units, plus $4.80 for the next and so on.
0-100 units =$5.00
101-200 units =$4.90
201-300 units = $4.80
301-400 units = $4.70
401-500 unites = $4.60
501 -600 units = $4.50
My formula is as follows, but I am not getting the correct total. Its close but needs to be exact. (It seems to be a rounding error somewhere?)
SUMPRODUCT(--(B3>={0,100,200,300,400,500,600}),B3-{0,100,200,300,400,500,600},{5.00,-0.10,-0.20,-0.30,-0.40,-0.50,-0.60})
B3 = total number of units sold
For the example: 367 units sold
Pricing would be:
100@ $5.00
100@ $4.90
100@ $4.80
67@ $4.70
Total: $1,784.90
When I apply the formula: SUMPRODUCT(--(B3>={0,100,200,300,400,500,600}),B3-{0,100,200,300,400,500,600},{5.00,-0.10,-0.20,-0.30,-0.40,-0.50,-0.60}) Total is : $1,754.80
What am I doing wrong?! Why is there a difference?
As I increase the units, the difference becomes larger, ie. 588 units sold, the error becomes larger. $2,796.00 versus formula answer of $2,608.00. When I attempt 202 units, where the answer is an obvious $509.80, the formula shows $999.40.