4 Replies Latest reply on Jul 10, 2017 10:07 AM by Jim Dehner

    Round Then Display As a String

    Kevin Warren

      Hello,

       

      I am having a difficult time displaying 1 decimal consistently for all outputs. Attached is a workbook with context, but the below portion of the calculation is what I believe needs to be tweaked in order to get the desired result.

       

      Max(IF RIGHT(STR(ROUND([Final Net Back % to display],3)),8) == "000001"

        THEN

        Ifnull(Left(STR(ROUND([Final Net Back % to display]*100,2)), Find(STR(ROUND([Final Net Back % to display],2)),".") + 2)+"%","N/A")

        ELSE

        Ifnull(Left(STR(ROUND([Final Net Back % to display]*100,2)),Find(STR(ROUND([Final Net Back % to display],2)),".") + 2)+"%","N/A")

       

      Thank you!

        • 1. Re: Round Then Display As a String
          Michael Hesser

          Hi Kevin;

          I can't say I understand exactly what your formula is trying to do, but one thing that stood out to me is your check for a 6-digit value when it appears you are creating 8-digit ones:

           

          Max(IF RIGHT(STR(ROUND([Final Net Back % to display],3)),8) == "000001"

           

          Should this be:

           

          Max(IF RIGHT(STR(ROUND([Final Net Back % to display],3)),8) == "00000001"

           

          Your THEN and ELSE parts of the equation are the same, too; maybe your actual code varies?

           

          Could you give us examples of where you see the rounding working inadequately and what you expect to see?

           

          Thanks!

           

          Thought: Are you checking for a "6-digit" value because you're actually checking for 100%? If this is the case, will your calculation return 00000100 or 000001? I am still a little mystified as to why your THEN and ELSE parts are identical.

          • 2. Re: Round Then Display As a String
            Jim Dehner

            Hi Kevin

             

            I'm not certain I understand what you are trying to show

            but have you tried  the portion of your formula that determines the Str value of the Final Net Back % as shown below

             

             

                                str((round(avg([Final Net Back % to display]) * 100,1)))

             

            you would have to wrap it your ifnull statements

             

            here is the result I got in the column labelled "Try this" it is shown as a discrete value

             

            Let me know if this helps

             

            Jim

             

                     

            1 of 1 people found this helpful
            • 3. Re: Round Then Display As a String
              Kevin Warren

              Thank you for helping me figure out how to display 1 decimal rounded and the null values as "NA".

               

              • 4. Re: Round Then Display As a String
                Jim Dehner

                Glad to help out Kevin -

                 

                Thanks for the badge - always appreciated

                Jim