It’s often the case that the more quantity you buy a product, such as a shirt, the lower unit price you’ll possibly get. The correspondence of quantity interval and unit price is shown in the following table:

Task: Given a purchase quantity, use Excel formula to calculate the unit price. The formula should be able to adjust itself according to different quantity intervals.

We can use nested IF function to achieve the static interval queries. As the IF function doesn’t support arrays, it’s hard to express a dynamic correspondence between interval and value.

An alternative to accomplish the task:

1.Start esProc (Download esProc installation package and free DSK edition licenseHERE. You will be prompted to load the license file when you run esProc for the first time).

2. Open Excel and load add-in ExcelRaq.xll through Option –> add-ins in Excel and click the Go button on the dialog. The Excel file is located in [esProc installation directory \bin]. Related information of Excel is easy to get if you don’t know how to load the add-in.

3. In Excel, enter the quantity of shirts you want to purchase in any cell (like A1) and type in the formula in another cell, like =esproc("=[15,13.75,13,12.5]([30,50,100,300,500].pseg@r(?))",A1), to calculate the unit price. For instance, by entering 50 in A1, you get 15 for the unit price, and by typing in 60 in the cell, you get 13.75. The pseg() function in the above formula returns the number of interval the specific quantity belongs to; the interval is by default a left-closed and right-open one; @r option denotes such an interval.

4. Or you can write the purchase quantity in the formula, such as = esproc (“=[15,13.75,13,12.5]([30,50,100,300,500].pseg@r(60)))”. Both can adjust accordingly when the quantity is changed, and thus modify the unit price or corresponding interval.