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.
You must be logged in to post a comment.