Calculate New Column From Scanning Individual Column Values

arshadsaad

Hello! 

I want to calculate a value in Total Cost column by scanning the formula in the formula column. 

For example: 

Formula column value: 

Unit Rate * (Distance_Kilometers - 20) / 100 * POW( MTOW_Metric Ton / 50, 0.5)

The Total Cost should be calculated by taking the value from the Unit Rate column and multiplying it with the value in the distance column and so on. 

Essentially I want to scan individual sections of the Total Cost column value and use it to multiply/divide/add/subtract values in other columns. 

One thing the formula needs to take care of this is: 

It should only calculate values for when MTOW of a particular falls between the values in the MIN and MAX column. Otherwise it should be zero. 

I would really appreciate any help

Sample Data.xlsx

JamesCharnley

Hi @arshadsaad,

The way to call a formula like that is to use the Dynamic Replace tool. The issue with your workflow is that the formula you're calling needs to be fully bracketed correctly, formatted exactly as your fields are within the workflow, so rather than me adding those brackets manually, it'll be best to do that wherever the formula is being pulled from so the workflow is still dynamic moving forward. 

I've attached an example of how the dynamic replace tool works, and more documentation can be found here: https://help.alteryx.com/20221/designer/dynamic-replace-tool#:~:text=Use%20Dynamic%20Replace%20to%20allow,on%20formulas%20or%20static%20values.

I think as for the point about being between the MIN and MAX, that can just be changed in a formula after the dynamic replace.

Dynamic replace formula.yxmd