Rounding to the nearest whole number

[markjp85]

Hi All,

I have a really simple rounding issue when using a calcualtion, please see the details below.

I am claculating the following [Quantity Ordered]/[Case Size] & want to round up to whole cases. I have tried using CEIL & ROUND which don't seem to work & return silly numbers so I am clearly missing something. An example of the calcuation is below,

302/6 = 50.3 - this needs to round up to 51.

Please can anyone advise me where this is going wrong? Any help would be appreciated.

Regards

Mark

[NicoleJohnson · Accepted solution]

Can you show us what sort of answers you are getting instead when using CEIL formula? This one should work: CEIL([Quantity Ordered]/[Case Size])

... so if that's not giving you 51, you may need to check to ensure that your two fields are a numeric data type - if one of them is a string, that might give you some incorrect results?

Let us know! 

NJ

[jdunkerley79 · Accepted solution]

Using a formula tool:

CEIL(302/6)

should return 51.

[JoshKushner · Accepted solution]

If you want to Round Up use: CIEL([num])
If you want to Round Down use: FLOOR([num])

[markjp85]

Thanks for your replies, I used this & it worked perfectly, I had a summerize in the wrong place in my workflow that was causing the numbers issue so everything is now fixed & giving me the correct data.

[Premyevale]

Hi,

I am wondering if I can use CEIL and floor together..

Like if its 55.4 the result should be 55 and if it is 55.6 then the result should be 56..

Regards,

Prem

[joeltrudel]

I would like to know this as well- I've run into several occasions where Alteryx rounds up/down (incorrectly) because I've used either Ceil/Floor and not both. Would like  logical rounding i.e.<.4 rounds down, .5 or < rounds up. Please let me know if this is possible or the best way to achieve this.

[ChrisTX]

Try the Round function

Round(3/8, .001) = 0.375

Round(3/8, .01) = 0.38

https://help.alteryx.com/current/Reference/Functions.htm

[Premyevale]

Round([FIELD_NAME], 1) 

We will get to the nearest Integer same as using round up and round down together..

Round([FIELD_NAME], 100)

We will get to the nearest 100

Round([FIELD_NAME], 0.01)

We will get to two decimal

CEIL([FIELD_NAME]/1)

This is similar to round up function

Regards,

Prem