Configuring the excel sheet
Once you have your Magento products created with your preferred custom fields added, it's time to configure the calculator using an excel sheet. The rules are simple:
- You will have the 'inputs' (values from your custom fields) available at column 'B' of the first sheet.
- The value mapped at each row will match the name of your custom field present at column 'A'. Please check the example below to understand how to map your inputs.
- You must return the result price at cell D2 of that same sheet.
- You must return if the product is 'orderable' or not at cell D3. You must return at this cell one of these values ( YES / NO ). If the product configuration introduced by user is not valid, you can return 'NO' and then the product page will not allow the 'add to cart' action with that configuration.
- You can return any information message you may wat to show at cell D4. You can use this cell to explain why the product is not orderable or introduce any other information.
That's all, you can use the rest of the sheet or any other sheets at the file to configure your product price calculator as you need. You can use all math operations and most of the functions as well, for ex:
- Math and trigonometry functions (ABS, FLOOR, EVEN, FACT, LOG, etc..)
- Lookup and reference functions (LOOKUP, INDEX, MATCH, etc...)
- Logical functions ( AND, IF, FALSE, NOT, etc...)
- Statistical functions (COUNT, MAX, etc...)
And many others, you can contact us regarding any specific function you may want introduce and we'll confirm if its available.
Example: We'll build the business card example price calculation you can check at the demo site:
You can start with this simple template.
At column A you will type the names of the inputs you want to use for your calculation. The ones of the image are available for default for every product, below you can add the custom fields you have for your product. For this case, we'll add the 3 custom fields of the product (Card Type,Sides, Rounded Corners)
Now you only need to use the values that will be mapped at column 'B' to handle them as needed, adding your calculations so the final result price shows on cell D2. Don't forget to return also the values for 'orderable' at cell D3 and any message you want to show at cell D4.
Each time an user changes the product configuration the values will be mapped at column B and the price from cell D2 will appear. You can try your excel typing manually some of the values and see the resulting price before attaching your excel to your calculator.Last modified on