Creating a Useful Spreadsheet From Miva Merchant Order Export Data: Step Three

Now that we have sorted out the tax, shipping and order totals, let’s take a look at how to combine the attribute and product pricing in a single column that can be summed to provide an accurate product sales total.

Step Three: Combine the Attribute and Product Prices

For the purposes of this explanation, let’s assume we are dealing with a clothing shop that sells pants that come in small, medium and large, and T-shirts that come in small, medium or large and are available in red or blue. We will deal with two orders, as follows:

The Product Price Formula

Because a product can have, theoretically, an unlimited number of attributes, several of which may affect the product price, there isn’t really a tidy way to combine the product and attribute prices. In our sample store, for example, the price of a T-shirt is affected both by the size and the color: medium shirts cost $0.50 more than small shirts, and blue shirts cost an additional $1.00.

Since there isn’t an easy way to create a single price for each product, we’re simply going to insert a price column that will list all attribute prices and all product prices one time each. Totaling this column will then give an accurate figure for total product sales.

1. Insert a new column beside PROD_PRICE by clicking the column heading immediately to the right (this will be the PROD_QUANT column), selecting the Insert menu, and clicking on Columns.
2. Name this new column TOTAL PRICE.
3. Click in cell AE2 and paste the following formula:
   =(IF((AG2=”"),AD2,AI2))*AF2
   
4. Drag and fill the formula into the remaining cells in the column.
5. You will note that the attribute prices appear first, multiplied by the quantity ordered. The base price of the product is listed below the attribute prices, again multiplied by the quantity ordered. The total shown is now accurate for the products ordered.
   

You can stop at this point if you don’t mind leaving the old, inaccurate columns in place. If you’d like to remove the columns containing the repeated data, proceed to Step Four.