This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
My parent workflow is pretty clean with just a data cleanse to remove white space, then my two iterative macros. My second macro has a debugging output which was useful to find where my order of operations was out in certain scenarios. Macro 1 Steps: 1) remove parentheses from any single number, e.g. (10) -> 10 2) parse the resulting text to split for the first operation found 3) if no operation is found, we only have a whole number so send it to output 4) calculate first operation and bring string back together. e.g. 4+6*3 -> 10*3 5) repeat until all operations processed
Macro 2. This is a little bit more involved due to the stricter order of operations. The steps here are: 1) remove single number parentheses 2) create waterfall of filters based on order of ops 2.1) any plain addition, e.g. 3*6+4 -> 3*10 2.2) contains addition but needs multiplication in parentheses done first. e.g. (3*6)+4 -> 18+4 2.3) only multiplaction left 2.4) only whole numbers -> output result 3) iteration outputs 3.1) send new strings back to repeat process 3.2) output all strings from each iteration, this helped with debugging and getting the parse logic correct for 2.2
I tried at first in macro 1 to do the regex parsing and function all in one formula tool along the lines of this
I'm trying to understand better how to replace macros with combinations of generate rows & multirows. Having fun even if the formulas inside are terrible to debug...(can't imagine inheriting one workflow where someone did something like this...)
Here's part 1. After making the effort to minimize the use of macros in previous days, I just gave in today and built an iterative one in about the same time that it takes me to ensure that the parentheses line up in nested IIF statements in the non-macro versions built previously. Runs quickly as well, 0.3s, since it only iterates once per level of parentheses and my input set was never nested more that 2 deep.