Rounding Sell or Buy Prices to the nearest increment using Excel + Mass Create
While the Crystal Commerce system does not have any easy method that allows users to round prices using a batch update, it is quite possible to round your sell or buy prices with a few quick steps in Excel.
1. Export your CSV: The first thing needed for this process is a CSV with current pricing data. In Inventory > Products, select the category for export, and hit the Export to CSV button. You will not need to export variant information to change product prices, so leave the variant specific filters blank. It may take a few minutes for this file to generate; you will be able to find it in Notifications > Generated reports when it is finished.
2. Edit your CSV: This exported CSV will include several columns that are not necessary for this procedure, so those columns and their contents should be completely deleted before moving on. The only column headers needed to modify buy and/or sell prices are as follows:
Category (Circumstantial, see below)
Only include the Category column if the CSV contains products from multiple categories. If the CSV is of a single category only, the entire Category column must be omitted - you will specify which category is to be updated later in the Mass Create process.
Now, you will need to enter the formula that will round your data values to the nearest selected increment. In an adjacent cell, add the formula for rounding to the nearest quarter:
In this example, the cell with the value I want to round is D2, so that is what I select and what appears in the gif. In reality, the cell your first value is in may not be D2, so your formula could have a different cell number where D2 appears here.
To repeat this formula along the entire sell/buy price column, either click and drag the lower right corner of the first cell, or double click that same area to auto-populate the entire column.
At this point, the rounded numbers are still just the solutions to the formula that you entered into the first cell, so you need to copy and "paste as values" to populate those cells with the actual numeric values they represent. After this step is completed, you may replace the original un-rounded price data with the rounded pricing information. Clear out any data in unused rows of this CSV, save, and you're ready to use the Mass Create tool.
Both repeating the formula and copy/paste as values are demonstrated here:
Update your pricing using Mass Create: In Inventory > Mass Create, only check the box for "Import contains multiple categories" if the CSV contains pricing data for multiple categories of products. If the CSV contains product data from just one category, select that specific category from the multi-select boxes.
Choose your new CSV file, and make sure to select "Only Update Products" as the import mode. Click Mass Import, and you will receive a notification in your admin once the CSV has completed updating all product prices.
To round to other increments of a dollar, use the following formula variations:
- Round to nearest 0.10 =ROUND(D2*10,0)/10
- Round to nearest 0.50 =ROUND(D2*2,0)/2
To always round up (e.g. have $.35 round to $.50 instead of $.25), you can use =CEILING instead of =ROUND:
-Round up to nearest $.50 =CELING(D2,0.5)
Round up to nearest $.49 or $.99 =CEILING(D2,0.5)-.01