Price breakpoints are a way for a business to encourage larger orders by offering lower rates when a customer purchases many units at once. The actual breakpoint is the minimum number of units a customer must purchase to receive the lower price per unit. Calculating breakpoints for a customer's purchase quantity can be done in Microsoft Excel 2010 by having the VLOOKUP function search for and return a value from a spreadsheet with information about your breakpoints and unit prices.

## Step 2.

Create a column containing text descriptions for each quantity range of your product. For example, type "Qty 1-9" in cell A1, "Qty 10-19" in cell A2 and "Qty 20+" in cell A3.

## Step 3.

Create a column containing the lowest value for each quantity range. These values represent the breakpoints at which a customer receives the next price level. For example, enter "1" in cell B1, "10" in cell B2 and "20" in cell B3.

## Step 4.

Create a column containing the unit price for each quantity range. For example, enter "15.00" in cell C1, "10.00" in cell C2 and "5.00" in cell C3. This means a customer purchasing between 1 and 9 units will receive a price per unit of \$15, a purchase between 10 and 19 units will use a price per unit of \$10, and a purchase of 20 units or more will use a price per unit of \$5.

## Step 5.

Enter the number of units your customer wants to purchase in an empty cell. For example, enter "11" in cell D1.

## Step 6.

Calculate the breakpoint for the customer's purchase quantity with the "VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)" function. The "lookup_value" is the cell containing the number of units the customer wants to purchase. The "table_array" is the range of cells containing the breakpoints and unit prices. The "col_index_num" is the number of the column in the "table_array" range containing the unit prices. The "range_lookup" must have the value of TRUE, which returns an approximate match from your range. Using the previous example, enter "=VLOOKUP(D1,B1:C3,2,TRUE)" in cell E1. This displays a value of "10," which is the price per unit for a quantity between 10 and 19 units.