Extracting logic from XLS to a web app

In Brezza Screens Systems we use custom software to generate quote estimates of our window screens (mosquiteros). This has enabled us to serve much more customers with increased quality standards and reliability.

Previously, when a customer would ask how much does a Window Screen costs, we would use Excel to quote an estimate. That process was slow and required slow thinking, meaning that it required high level of focus to avoid making any mistake. Even though Excel is a powerful app, it is very easy to accidentally mess up a formula. Additionally, there is a potential risk when copying an existing Excel sheet to reuse it for another customer.

Case: Esperto Window Screen

In this case there is a group of formulas to calculate the amount and length of threads that we should consider in our model Esperto Window Screen

Example of XLS formula to calculate strings

These are the formulas for the length each of the 5 threads.

Example of how to understand a messy spreadsheet formula

In this case the formula is hard to read because it has a lot of conditionals and it is all in one line.

Formatting the formula allows an easier understanding. I use VSCode for that. I also replace the cell numbers with variable names. In this case I replaced `I4` for `height`.

Then I flatten the structure so it is easier to read.

Now it is easier to see how many threads should there be for each threshold of height size. Finally I rewrite this formula in Rescript and I plug it into our quote estimator app to have that logic in our pricing calculations.

This way we have the logic in an app that can display only the details that the user needs to make a quote. The calculation logic is protected from any human error from the user by moving it into Rescript code and writing unit tests that will run automatically during development and before publishing a new version of the app. This makes our quotes quicker and more reliable.