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.
Creating the Price Calculator price engine
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.
Square Foot Calculation
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.
Square Inch Calculation
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.
Handling decimals with the square inch calculation
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.
Price Calculator versus Price Spreadsheet price engines
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