Interesting INT() Issue Between Cube and Excel
I don’t know about anyone else, but I do a lot of calculation prototyping in Excel before applying that in scripts. Â One of the most recent was to do a script to add expansion zones (also known as “dummy zones”, although they aren’t really dumb, just undeveloped!).
The problem I had was related to the following equation:
R=INT((819-N)/22)+1 Â Where N={820..906}
In Excel, the results are as below (click on it if it is too small to see):
In Cube, I got the result of (click on it to expand, and I only took it into Excel to move stuff around and make it easier to see):
Note the sheer number of zeroes in the Cube version and all the numbers are ‘off’.
The reason, as I looked into things was because of how INT() works differently in the two platforms. Â In Cube, INT simply removes everything to the right of the decimal, so INT(-0.05) = 0, and INT(-1.05)=-1. Â In Excel, INT rounds down to the nearest integer. Â This means that negative values will be different between the two platforms. Â Note the table below.
| Excel | Cube | |
| 3.4 | 3 | 3 |
| 2.3 | 2 | 2 |
| 1.1 | 3 | 1 |
| 0.5 | 0 | 0 |
| 0 | 0 | 0 |
| -0.5 | -1 | 0 |
| -1.1 | -2 | -1 |
| -2.3 | -3 | -2 |
| -3.4 | -4 | -3 |
While neither software is truly wrong in it’s approach (there is no standard spec for INT()) it is important to know why things may not work as expected.
