5 Replies Latest reply: May 14, 2015 9:55 AM by Sunny Talwar RSS

    Problem with rounding

    Matt Maino

      Hi everyone,

       

      I am working with a calculated field that contains several decimal places. I need to create a pivot table with buckets based on this calculated field's value, so my first thought was to round the number to two decimal places so that it would be easier to work with. This is what I did:

       

      round(calculation, 0.01)

       

      This appeared to work, but then when I used this function to group rows into buckets in my pivot table some of the values were not picked up by my logic and I can't figure out why. This is what the calculated dimension looks like:

       

      =if(round(calculation, 0.01) < 0.6, '< 60%',

      if(round(calculation, 0.01) >= 0.6 and round(calculation, 0.01) <= 0.65, '60-65%',

      if(round(calculation, 0.01) >= 0.66 and round(calculation, 0.01) <= 0.70, '66-70%',

      if(round(calculation, 0.01) >= 0.71 and round(calculation, 0.01) <= 0.75, '71-75%',

      if(round(calculation, 0.01) >= 0.76 and round(calculation, 0.01) <= 0.80, '76-80%',

      if(round(calculation, 0.01) > 0.8, '> 80%', 'no value'))))))

       

      Does anyone know why some rows are showing up as 'no value' when they should be showing up in the other buckets? Could it be because round() isn't actually converting the number? If so, how do I convert the number to be only 2 decimal places?

       

      Thanks

        • Re: Problem with rounding
          Sunny Talwar

          That is weird. Can you share a application where it is doing this? Only thing I can think of which might be throwing this off could be that the calculation has numbers and text in it. So when its number it goes in the right bucket, but when its text it is not able to round and is being pushed to 'no value'

           

          Best,

          Sunny

            • Re: Problem with rounding
              Matt Maino

              The values that are wrongly being placed in the 'no value' bucket are on the boundaries of each bucket - so some of the .6, .65, .66, .70, .71, .75, .76 and .80 vaues. This makes me think that the round() function is not actually changing the value, just displaying it differently

                • Re: Problem with rounding
                  Sunny Talwar

                  If that is the case, then may be you can use this instead:

                   

                  =if(round(calculation, 0.01) < 0.6, '< 60%',

                  if(round(calculation, 0.01) >= 0.6 and round(calculation, 0.01) < 0.66, '60-65%',

                  if(round(calculation, 0.01) >= 0.66 and round(calculation, 0.01) < 0.71, '66-70%',

                  if(round(calculation, 0.01) >= 0.71 and round(calculation, 0.01) < 0.76, '71-75%',

                  if(round(calculation, 0.01) >= 0.76 and round(calculation, 0.01) < 0.81, '76-80%',

                  if(round(calculation, 0.01) >= 0.81, '> 80%', 'no value'))))))

                    • Re: Problem with rounding
                      Sunny Talwar

                      Or for more accurate results:

                       

                      =if(round(calculation, 0.01) < 0.6, '< 60%',

                      if(round(calculation, 0.01) >= 0.6 and round(calculation, 0.01) < 0.655, '60-65%',

                      if(round(calculation, 0.01) >= 0.655 and round(calculation, 0.01) < 0.705, '66-70%',

                      if(round(calculation, 0.01) >= 0.705 and round(calculation, 0.01) < 0.755, '71-75%',

                      if(round(calculation, 0.01) >= 0.755 and round(calculation, 0.01) < 0.805, '76-80%',

                      if(round(calculation, 0.01) >= 0.805, '> 80%', 'no value'))))))

                • Re: Problem with rounding
                  Michael Solomovich

                  Try:

                   

                  =if(round(calculation, 0.01) < 0.6, '< 60%',

                  if(round(calculation, 0.01) >= 0.6 and round(calculation, 0.01) < 0.65, '60-65%',

                  if(round(calculation, 0.01) >= 0.65 and round(calculation, 0.01) < 0.70, '66-70%',

                  if(round(calculation, 0.01) >= 0.70 and round(calculation, 0.01) < 0.75, '71-75%',

                  if(round(calculation, 0.01) >= 0.75 and round(calculation, 0.01) < 0.80, '76-80%',

                  if(round(calculation, 0.01) >= 0.8, '> 80%', 'no value'))))))

                   

                  Besides, I don't see need for rounding at all here (?)

                   

                  Edit: fixed boundaries