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.

Open your spreadsheet in Microsoft Excel 2010.

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.

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.

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.

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

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.