Do you like formulas and calculations in spreadsheets? I do. It is simple and easy. I’m sure you use them often. Considering how many openBoM users export data to Excel in order to perform calculations, we decided it was time for openBoM to become a bit smarter and begin having capabilities to perform calculations as well.

In the last production update (June 2017), openBoM formulas and rollups made their first appearance. If you missed that particular update, check my previous blog for more details. And here’s a video:

Today, I want to provide you a bit more information on how to use the formula builder. I will use the following simple BOM as an example:

Let’s say you want to create a formula to calculate the Total Cost of Part Number 100–1. The formula would consist of Total Cost = (100–1)(Quantity) * (100–1)(Cost). Note that Total Cost, Quantity, and Cost are numeric properties in the BOM. Here’s how the formula would look like using the Formula Builder:

You can copy & paste formulas. openBoM will adjust part numbers accordingly.

You probably noticed a [check box] applied to all rows. It is useful when you need to apply or update formula for all the rows in your BOM.

Another useful macro is @pn, which can be used to identify Part Number in the current row when you add a formula.

Note: In the current release you still need to apply a formula to all rows if you add a new Item. However, in one of our future updates, we will solve this by creating an option to build a formula for an entire property.

There is a way to define a rollup as well. To define one, you need to use the macro (SUM) to make a calculation.

Oleg, July 2017