Calculation Formula Help

[icf_Eric_Link]

Hello, I'm hoping that someone can help me with how to setup a calculation.  We are taking some field samples and depending on the initial sample value we need to apply a certain calculation if it falls within a certain range.  I'm sure we need to use if statements but not sure exactly how to do it.  Below are guidelines parameters for calculating the various ranges.

Basically if we get an initial sample value of 2.5 then we need to calculate the calculated turbidity by adding 1 so the new calculated value is 3.5. I'm hoping to have a single question be able to evaluate the original sample value and then apply the various calculations based on the table below.  Any help is greatly appreciated.  Thanks

[DougBrowning · Accepted solution]

You just need to nest if statements in the else part like this.  Note your chart has logic errors since you have >= 5 and <= 5 which means both are true.  I assumed < 5 on the first.  All of your numbers have this issue.

if(${NTU} < 1, ${NTU} + 2, if(${NTU} >= 1 and ${NTU} < 5, ${NTU} + 1, if(${NTU} >= 5 and ${NTU} < 50, ${NTU} * 1.2, if(${NTU} >= 50 and ${NTU} < 100, ${NTU} + 10, ${NTU} * 1.1))))

Note the above assumes the field is required.  If not add a check for "" otherwise it will get the last calc.

You can add another if like  if(string-length(${NTU}) > 0, 0, rest above  Assume you want a 0.  You could also have a relevant on the calculation so it does not calc until they fill out NTU.

hope that helps

[icf_Eric_Link]

Thank you Doug!  This is super helpful and got me what I needed.  Much appreciated.

[Katie_Clark]

I think something like this should do it. It can help to write out your expressions like this to help visually group the conditional statements. It's also very helpful to write within a text editor that uses syntax highlighting so you can keep track of matching parentheses. 

Note that line 5 is the final "Else" part of the if-statements. 

if(${UT} < 1, ${UT} + 2, 
  if((${UT} >= 1) and (${UT} <= 5), ${UT} + 1, 
    if((${UT} > 5) and (${UT} <= 50), ${UT} * 1.2, 
       if((${UT} > 50) and (${UT} <= 100), ${UT} + 10, 
         ${UT} * 1.1
        )
     )
  )
)

[icf_Eric_Link]

Hi Katherine,

Thank you for your providing this visual.  It does indeed help with visualizing how to write the statement.  I'll make sure to use this in the future.  Thanks again!