Three quantity columns in a price engine can be used to get a square foot, or a square inch calculation when required by a product you are creating.

Price engines can use up to five *Quantity Parameters*. The quantities can be whole integer values or decimal values, depending on how the worksheet for the price engine is setup. The number of quantities and the ranges of the quantities are defined in the worksheet for the price engine when it is created.

Go to the menu item *Preferences > Price Engines* in your account, use the green *Add New* button and choose *Calculator*. Give the new price engine some *Name*, and then hit the green *New Calculator* button. That opens a window where you can define the quantity columns, grid rows, and calculator parameters for the new price engine.

For a square foot (or square inch) price engine, you will Define three *Quantity Parameter *fields for the new price engine. No *Grid Parameters* will be used when creating this price engine. The *Calculator Parameters* will be added directly to the worksheet later on in this example. The *Grid Parameters* and *Calculator Parameters* can be left empty.

Once the *Quantity Parameters* have been entered, scroll to the bottom of this window to use the green *Generate Excel File* button to download the worksheet. It should look like this when opened.

The three *Quantity Parameter *columns will be used for the square footage calculation.

*Q1*as Number of banners*Q2*as Length in feet*Q3*as Height in feet

Add one *Calculator Parameter* row to the spreadsheet as "Price". The name is not important, since this option will not be shown to the user ordering the product, the row just needs to have a name under *ParameterSet*. If multiple calculator rows were being added, to have different levels of pricing based on quantity for example, the name "Price" would be added to each calculator parameter row in the *ParameterSet *column.

- The
*Per Thousand Weight*is the weight of 1000 units, or in this case, the weight of one thousand feet of material. *Show As Option*will be*No*, as the calculator row is only used for calculating the price based on the three quantity columns and not displaying any options to the user ordering the product.- The
*Applies To*column will use the calculation Q1*Q2*Q3. *Billing Type*will be*Single Unit*. The*Cost Per Billing Unit*will be the cost of one square foot of the material.

The finished square foot worksheet should look like this. The quantity, height and length inputs will be calculated together and will multiply times the amount in the *Cost Per Billing Unit *column to get the final price.

For a square inch calculation scenario, all the steps noted above will be the same, but the calculation will be slightly different. The sum of *Q1*Q2*Q3 *will need to be divided by 144 (1 square foot is 144 inches).

When you first download the price calculator worksheet generated from Excel, the calculation for *Q1*Q2*Q3* will be present, but the variation of *Q1*Q2*Q3/144* will not be present on the dropdown menu of the *Applies To* field.

The available calculations are shown by scrolling down the worksheet. The values starting at about line 86 and shaded in gray, are the calculations that are available in the *Applies To* column above. Any of those calculations can be adjusted there and referenced on the *Applies To* column on the* Calculator Parameter* rows.

In this case, the *Q1*Q2*Q3* calculation was copied and pasted and set to be *Q1*Q2*Q3/144*. Once the calculation is showing correctly in this area of the spreadsheet, return to the *Calculator Parameter* row and the new calculation will be available in the drop-down menu in the *Applies To* column.

When using the calculation *Q1*Q2*Q3/144* to get the square inch calculation, note that the return may come back as a decimal value. If the *Min Qty* and *Max Qty *columns are being used to change the price based on the quantity being ordered, it is important that the quantity columns also be in decimal values.

In the example above, the *Min Qty* and *Max Qty *columns specify breaks as 1-17, 18-100, 101-500, 501-1000 and 1001 to 10000. For example, returns such as 17.03, 100.12, or 500.234 fall outside of the specified integer value ranges for the quantities and would cause the price engine to throw an error of "That combination of choices not allowed".

To account for this, one approach is to make sure that the quantity values are specified as decimal values where appropriate in the *Max Qty* column.

Another apprach to solving this situation is to adjust the calculation itself to always return an integer value. The *Applies To* column in the pricing calculator worksheet allows some limited use of Excel functions.

The formula *Int(Q1*Q2*Q3/144) *will respond in the *Applies To* column. This would ensure that the numbers being returned from that calculation are always rounded whole number values without decimals.

Calculations in the *Applies To* column are generally not recommended for Price Calculator price engines. Some strategies may work, like the Int() trick above, but many will not work. A Price Spreadsheet price engine can make more robust use of Excel calculations when creating a price engine for your products. For more information on Price Spreadsheet price engines, please see the chapter **Ch. 014. Creating a Spreadsheet Method Pricing Engine**

For more information on creating Price Calculator Price Engines, please see the chapter **Ch. 013. Creating a Pricing Calculator Pricing Engine**

Help Desk Software Powered by SmarterTrack