A deep dive into the openBoM calculations and formulas

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, 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 [x] apply to all rows flag. It is useful when you need to apply or update formula for all rows in your BOM.

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

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

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

Conclusion. I hope you’re as excited as I am and find formulas support in openBoM useful. In the next few blogs, I will discuss how to use formulas together with Item Master (catalogs) and together with openBoM CAD integrations.

Have a good weekend!

Best, Oleg

PS. We should know each other better. If you live in a Greater Boston, please let’s have a meeting (coffee is on me). If you’re located in other places, let’s have a virtual coffee session — I will figure out how to send you a real coffee for our virtual coffee session.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade