Problem with rounding

[#1]

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

[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

[Anonymous]

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

[#4]

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

[sunny_talwar · Accepted solution]

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'))))))

[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'))))))