Select to view content in your preferred language

Calculation Formula Help

457
4
Jump to solution
08-18-2023 09:36 AM
icf_Eric_Link
New Contributor III

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

icf_Eric_Link_0-1692376263790.png

 

0 Kudos
1 Solution

Accepted Solutions
DougBrowning
MVP Esteemed Contributor

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

View solution in original post

4 Replies
DougBrowning
MVP Esteemed Contributor

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
New Contributor III

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

Katie_Clark
MVP Regular Contributor

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
        )
     )
  )
)

 

Best,
Katie


“The goal is not simply to ‘work hard, play hard.’ The goal is to make our work and our play indistinguishable.”
- Simon Sinek
0 Kudos
icf_Eric_Link
New Contributor III

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!

0 Kudos