It works on this particular case, don't know how my multi-row formula would perform on values like CXCIV...
Also, did not know about the dynamic replace tool and its abilities; nice to learn about it when reading other people's solutions, used a batch macro for this myself.
Nice one, I liked this challenge!
This was fun!
First I created an additional reference table with Roman abbreviations - this includes IX for 9, and for scrupulous I added others as well (IV, XC, CM, etc). The values are modified to have a separator before them (,). I then used find/replace on abbreviations first and then on numbers on the formula operators in column. Summarize to get the total number for each one. Replace in the original formula. I see many people used Dynamic Replace - I went ahead with a very simple R script to evaluate a string as formula (eval(parse(text=formula))). Will look into Dynamic Replace for the next time!
That was great! I really enjoyed that
Behold! Two Roads to Rome in one... I took the long way around the first go-around with an obnoxiously If-Statemented Multi-Row Formula... and then read a few of the previous posts and had the smack-the-forehead moment of Dynamic Formula realization... which ended up in a slightly longer but more "advanced" solution. So I included both.
The last step is indeed the most delicate one!
Using R may be the obvious way to evaluate the equation/formula, but definitely not prefered.
"Dynamic replace" is much better in terms of performance and appropriateness for this task. Worth learning and exercising!