5 Replies Latest reply on Jan 15, 2017 5:39 PM by John Linville

    Parameters ('Float') and Calculating Strings

    John Linville

      This is likely a bug--maybe a known bug...


      I have several parameters that are floating numbers that are formatted as percentages. I have a need to turn these in strings in calculated fields to add into the tooltip (based on a number of factors). The problem is that it is hit or miss whether the parameter will passed through as entered, or if it will add a bunch of decimals and round (seemingly) at random.


      For example, 0.154 will show as such if converted to a string. 0.077 will show as 0.0770000002...


      I have attached a workbook to illustrate this. I have also submitted a case, in case it is a bug. I am hoping someone has a solution for this (or a workaround, at least). I need to display the parameter as a percentage in a string (e.g. 0.154 as 15.4%). Rounding and other number functions work on the parameter when used as a number, but seem to get stripped when converting to a string.


      Any ideas would be appreciated.

        • 1. Re: Parameters ('Float') and Calculating Strings
          Chris McClellan

          Floating point are just that .... it's the classic old 22/7 = and the answer keeps on going forever.  That's usually a good thing, but you need it rounded and formatted nicely.


          You're on the right track with your workbook, I'd just change the last calculation to :


          LEFT(STR([Float Mess]*100),FIND(STR([Float Mess]*100),'.')+1)


          to cater for the unknown number of digits to the left of the decimal place.


          Apart from that, you're on the right track, but yes it can be painful converting floating point to strings at times but get the formatting right and it will work

          1 of 1 people found this helpful
          • 2. Re: Parameters ('Float') and Calculating Strings
            John Linville

            Hi Chris,


            Thanks--that is the way I was working. The issue is, if you change the parameter to 0.022, then your solution gives me 0.021, rather than the 0.022 that I need. It also doesn't really explain why 0.154 is not 'padded' with extra decimals (that change the rounding). In all cases in my WB, the parameters are all 3 decimals--some are padded, others are not.

            • 3. Re: Parameters ('Float') and Calculating Strings
              Chris McClellan

              Interesting ... the problem I fixed was when you have 15.4 but your code was only doing 15.  (because you were doing a LEFT(X,3)).


              I also added the value of 0.077 to the parameter seeing you were talking about that in your first post.


              So, what are you really after ?  just the number formatted nicely as a percentage (as in X.XX%) or do you need all the steps in between as well ?

              • 4. Re: Parameters ('Float') and Calculating Strings
                John Linville

                Basically, the main thing I am after is the parameter (whatever is in it) formatted as a percentage in a string...

                • 5. Re: Parameters ('Float') and Calculating Strings
                  John Linville

                  This seems to work... A LOT of work for something that SHOULD be simple... You put me on the right track, though! Thanks!


                  IF NOT MID(STR([Float Mess]*100),FIND(STR([Float Mess]*100),'.')+2,1) ="" THEN

                      IF INT(MID(STR([Float Mess]*100),FIND(STR([Float Mess]*100),'.')+2,1)) < 5

                          THEN LEFT(STR([Float Mess]*100),FIND(STR([Float Mess]*100),'.')+1)

                          ELSE LEFT(STR([Float Mess]*100),FIND(STR([Float Mess]*100),'.'))

                              + STR(INT(MID(STR([Float Mess]*100),FIND(STR([Float Mess]*100),'.')+1,1))+1)


                  ELSE STR([Float Mess]*100)