Calculations based on text
I have an issue regarding how to set up a formula to get a calculation to work. I have a mapping module, where you can enter how a specific calculation should be done, looking like below:
|Revenues||# units sold||*||average price per unit||BLANK||BLANK|
The format of the line items component_1, 2 3 as well as calc_1 and 2 are list. One list containing the drivers and another containing the different calculation methods (+, -, / and *). The dimension of the module is the same as for the component line items, containing the drivers.
Let's assume component_1=10 and component_2=10. I then have another module, where I collect the values for the different components, looking like the following:
I now have the values and the calculation method to use, but the problem is the formula for "RESULT". I obviously want it to be dynamic, so that if I would have - instead of *, it would work automatically, all depending on the first module and what you have chosen there. Does anyone know what this formula would look like?
Also, if I were to have a component_3=5 and perhaps have a mix of multiplication and addition, for example have a module 2 looking like the following:
Then I obviously want the formula to understand that * comes before +, so that the result is 5*20 + 10 = 110 and NOT 10+20 * 5 = 150. What would this formula look like?
Any help or guidance is highly appreciated, thanks a lot in advance!