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 unit-cost 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 unit-cost, 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 unit-price 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:F10-F5:F9,$B$5-F5:F9+1)>0,
IF(B$5>=F6:F10,F6:F10-F5:F9,$B$5-F5:F9+1),0)*G5:G9)

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 1-9 interval, 20 units in the 10-29 interval, and 6 units in the 30-35 interval, and 0 units in both the 60-100 and 100-9999999 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)