Progressive Pricing Formulas For Excel
In many
business, especially manufacturing and wholesaling, the unit price of an
item may determined by the number of units purchased. A widget may
cost $2.00 when 1 to 9 are purchased, but may cost $1.50 when between 10
and 99 are purchased, and so on, with greater discounts given to larger
quantities.
There are two methods to determine the total cost of the order. The simpler is when the the unit price is the same for all units in the order, and depends only on the number of units ordered. For example, this would be the case if 50 widgets were purchased each at $1.50, where the $1.50 unit cost came as a result of the total quantity of 50. Here, the unit cost can be determined by a simple VLOOKUP function (as will be shown later), and the total cost of the order is simply unitcost times quantity, or $1.50 * 50 = $75.00. However, it becomes more difficult when the unit price is distributed across the order. For example, suppose that in a order of 50 widgets, the first 9 are priced at $2.00, and the remaining 41 are priced at $1.50. In this example, there is no single unit cost. The total cost of the order is ($2.00 * 9) + ($1.50 * 41) = $79.50. While it is simple enough to do this example by hand, it becomes more complicated when there are a large number of quantity ranges, each with a different unit price. This page describes formulas for Excel that can be used to determine these costs. We'll call the first method "Bracket Pricing", in which the total order is based on a single unitcost, and we'll call the second method "Progressive Pricing" in which the price of each unit sold is based on where it falls into various "quantity intervals". 

Bracket Pricing



Suppose we have a table of quantities and unit prices, as in the table to the left. The numbers in the Quantity column indicate the minimum number that must be purchased to receive that unit price, for all the units in the order. For example, for orders of 1 to 9 units, the price for each is $5. For orders of 10 to 29 units, the prices for each is $4. Since the price is for all the units in the order, an order for 25 units would have a total of $100, since each of the 25 units is priced at $4.  Quantity  Unit Price 


1  $5  
10  $4  
30  $3  
60  $2  
100  $1  
We can use a simple VLOOKUP
formula to return the unit price, for a given quantity. If the table
of quantities and prices is in cells F5:G9 (the column headings
are not included in this range) and the number of units ordered is in cell
B5, the following formula will return the unit price for the order.
=VLOOKUP(B5,F5:G9,2,TRUE) If this formula is in cell B6, then we can simply multiply B5 by B6 to get the entire order amount. =B5*B6 There is nothing particularly difficult in either of these formulas.


Progressive Pricing


In the Progressive Pricing method, there is no single unitprice for the entire order. Each unit ordered is priced according to the "quantity interval" in which it falls. If 25 units are ordered, the first 9 may be priced at $5, and the remaining 14 may be priced at $4. If 100 units are ordered, there the total order may be distributed across several unit prices.  
Suppose we have a table of quantities and unit prices, as in the table to the left. This is the same table in the Simple Pricing section above, except that we have added an absolute maximum number at the end of the Quantity column. The numbers in the Quantity column indicate the minimum number that must be purchased to receive that unit price. But only those units receive that price.  Quantity  Unit Price  
1  $5  
10  $4  
30  $3  
60  $2  
100  $1  
9999999  
Suppose this table is in cells F5:G10 (the column
heading are not included in this range). The final entry in the
Quantity list must be larger than any actual quantity that will be ordered.
(What this number will actually be depends on what you're selling  if
you're selling Space Shuttles, it will probably be quite low, if you're
selling paper clips, it will be somewhat higher.)
It does not matter what value you put into the Price column for this "maximum value" row. It can be left blank or zero  it is never used in the calculations. The formula below will determine the total cost of the order, where the number of units ordered is in cell B5 and the table is in cells F5:G10. =SUM(IF(IF(B$5>=F6:F10,F6:F10F5:F9,$B$5F5:F9+1)>0, This is an array formula, so you must press Ctrl+Shift+Enter rather than just Enter when you first enter the formula, and whenever you edit it later. Click here for more information about array formulas. The formula works by repeatedly comparing the quantity in B5 to the values in the quantities column. It first builds an array that contains the number of units in each quantity interval. The formula actually references the quantity list in two ways: as F5:F9 and as F6:F10. Each cell in the second reference is simply the "next higher" quantity as the cell in the first list. This explains why we need the 999999 entry at the end of the list. The second reference to the quantity list must be greater than any possible quantity that might actually be purchased. The array that is build by the formula indicates the
number of units in each interval. For example, if the number of units
ordered were 35, we'd have an array like {9;
20; 6; 0; 0}, which indicates that there are 9
units ordered in the 19 interval, 20 units in the 1029 interval, and 6
units in the 3035 interval, and 0 units in both the 60100 and 1009999999
intervals. Next, this array is multiplied by the array or range of
prices. This array is {5; 4; 3; 2; 1},
simply the prices from column G. If you are familiar with array
formulas, you know that the result of multiplying two arrays is simply
another array, each of whose elements is the product of the corresponding
elements in the first two arrays. So, the result of the multiplication
is the array {45; 80; 18; 0; 0}.
Finally, we use the SUM function
to add up the entries in this array, and arrive at the result of $143, or (9
* $5) + (20 * $4) + (6 * $3) + (0 * $2) + (0 * $1). 

The concepts here can be extended to any number of additional situations, such as figuring the shipping charges on an order of goods, or even figuring your income tax based on progressive tax brackets. Remember that in the formula above, the we use +1 because in this example units are ordered in "increments" of 1. If you're "quantity" values are dollar amounts, you'll want to change the "+1" parts of the formula to "+0.01", because the "increment" of financial amounts is a penny, not a dollar.  
(Thanks To SSP for definitions of terms) 