Ceiling / Floor problems

[avicnellyy]

Hi all

Hoping someone can help. I cannot add the QV file due to data protection, but i have exported the issues to excel and attached. See references for details.

I need to work out how many pallets we have in stock - to do this I am dividing total stock by 880. I need to round UP to the nearest pallet. Using a ceiling in the expression (pivot table) didn't work so I have added it into my LOAD script.

quantity,
ceil((quantity/880),1.0) AS ceilquantity

This gave me excel file v3 attached. Column F is what the ceiling is giving me. Column I is what it SHOULD be giving me. If I use row 7 as an example, 63880/880 = 72.59 so why is it rounding to 74, rather than 73?

I found another post on here that used a floor instead so I changed my LOAD script to this.

quantity,
floor((quantity/880)+0.50,1.0) AS floorquantity

This gave me excel file v4 attached. Column F and I are as above - but as you can see this gives different answers.

Any ideas?

Thanks,  Amy

[Vegar · Accepted solution]

The excel files, are they the output of aggregations of multiple rounded figures?

Compare the two variants of the same source values below.
3.19 + 4.40 = 7.59 rounded up is 8

3.19 rounded up is 4
4.40 rounded up is 5
4 + 5 = 9

[avicnellyy]

Hi

I didn't think they were, but i am starting to think this might be the reason. I have decided to accept this is the case and assume it is OK. Rightly or wrongly.

They are pulling through from SQL tables so it's not 100% clear what is in the background.

Thanks for the reply.