Select to view content in your preferred language

# Calculation Formula Help

457
4
08-18-2023 09:36 AM
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

1 Solution

Accepted Solutions
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

4 Replies
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

New Contributor III

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

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