I offer a price break after every 200 units a customer buys. So the first 200 units are $1,500 each, the next 200 units are $1,400 each, and so on.
In a given year, a customer may buy 300 units, so the first 75 may be at the price they were paying the year before, then they reach a price break, get 200 more at a lower price, and then the last 25 at an even lower price.
I know there must be a more elegant way to calculate the total sales for a year than the way I have done it in the attached workbook. The only thing I could think to do is create a table with 1000 rows in it, put the price for every individual unit in the table, and add them up with a SUM(INDEX) formula.
Can anyone help me calculate the totals in cells E3:J3 without creating a 1,000 row table?
Bookmarks