2 Replies Latest reply: Sep 9, 2014 11:56 PM by Enrique Colomer RSS

    Using Rounded Variables in Expressions

      Hello all! I have a question about rounded variables...

       

      I've created 2 variables, Variable A and Variable B, and in the expression I've rounded the number to 1 decimal place ($8.1M, for example).

       

      I then created a text box that will display different text based on whether Variable A is >, <, or = Variable B.

       

      The catch is that I want to determine whether ROUNDED Variable A is >, <, or = ROUNDED Variable B, because very likely, Variable A will never be exactly equal to Variable B.

       

      For example:

       

      Variable A = $8,059,000

      Rounded Variable A = $8.1M

       

      Variable B = $8,112,000

      Rounded Variable B = $8.1M

       

      Technically, in this example, Variable A < Variable B, however, in the text box I want them to be shown as equal because of the rounding factor. Does anyone know how to create a formula that takes into account the rounded numbers?

       

      My actual equation is here:

       

      ='Total ' &

       

      if(vA>vB,

        'is growing, increasing from '& vB & ' to ' & vA,

       

        if(vA<vB

      ,'is declining, shrinking from '& vB & ' to ' & vA

       

        'has not materially changed in the last period')

       

      I'd appreciate any help!

        • Re: Using Rounded Variables in Expressions
          Jonathan Poole

          use the round() function with a divide by 1,000,000

           

          replace

          vA

          vB

           

          with

          round(  vA / 1000000 , .1) * 1000000

          round(  vB / 1000000 , .1) * 1000000

           

          (I think for comparison purposes you actually don't need to multiple by 1000000 again. the 2nd parameter indicates i'm rounding to 1 decimal place. rounding is different than formatting to 1 decimal)

           

          so....

           

          ='Total ' &

           

          if(  round(  vA / 1000000 , .1) * 1000000  >    round(  vB / 1000000 , .1) * 1000000,

            'is growing, increasing from '& round(  vB / 1000000 , .1) * 1000000 & ' to ' & round(  vA / 1000000 , .1) * 1000000,

           

            if( round(  vA / 1000000 , .1) * 1000000< round(  vB / 1000000 , .1) * 1000000

          ,'is declining, shrinking from '& round(  vB / 1000000 , .1) * 1000000 & ' to ' & round(  vA / 1000000 , .1) * 1000000

          • Re: Using Rounded Variables in Expressions
            Enrique Colomer

            I you have rounded the variables in your creation, the value of both are equal, ($8,1M). you can try putting in a text box and the value of another variable with 2 decimal format aver that result.

            But if you have variable with their original value A is less than B

            I suggest you keep the values of A and B and original rather than rounding formatees field when present, and in this case, use 2 decimals for the text consistent