Rounding .775

[wbf]

Guys, found out something earlier

Try this:

Code: Select all

nNumber = 4283.775
nRounded = ROUNDP(nNumber, 2);
sRounded = NumberToStringEx(nRounded, '#.00', '.', '');

ASCIIOUTPUT('D:\TM1\Log\test.txt', NumberToString(nNumber), NumberToString(nRounded), sRounded);

You will get 4283.775, 4283.77, 4283.78

A colleague of mine enlightened me that this figure with decimal when stored in TM1 as floating, it doesn't really get stored as 4283.775 but 4283.77490234375, which is why the roundp seems like it's not doing it's job.

Sounds to me that we shouldn't be using roundp if there is a chance of hitting such jackpot figure. Instead, we should do StringToNumber(NumberToStringEx(nRounded, '#.00', '.', '');

Any thoughts?

[qml]

A colleague of mine enlightened me that this figure with decimal when stored in TM1 as floating, it doesn't really get stored as 4283.775 but 4283.77490234375, which is why the roundp seems like it's not doing it's job.

Where did you (or your colleague) take the "4283.77490234375" from? IEEE Standard for Floating-Point Arithmetic (IEEE 754) in its "double-precision" 64-bit incarnation offers at least 15 (usually 16) meaningful digits of precision, so it is not plausible that this would be the decimal representation of 4283.775 after conversion. However yes, it is possible that it is stored as a value slightly above or below the "intended" value, which is expected because the floating point representation does not allow for precise encoding of every value. Some decimal numbers stored as floats simply cannot be expressed as an integer multiplied by an integer power of 2 - and this is what this number representation is at its core.

However, I don't believe this is what is causing your rounding conundrum. TM1's explicit rounding functions like ROUND or ROUNDP use the round down approach, unlike Excel and TM1's formatting functions, which use the round to nearest method (with round away from zero as the tie breaker).

Just a few quirks of TM1 for you.

[wbf]

Thanks for the explanation.

I pointed this out as interesting because the fact that TM1 is storing the value slightly higher or lower in float is what is causing the inaccuracy of rounding using ROUNDP.

Well perhaps it is a no-brainer and I am guessing this is known to everyone (because I can't seem to find any post about this), but trying to reconcile and find this 0.01 variance for my client has got me stuck for quite a while. And of course, the client would care less of how floating or decimal works, as long as the figures are right, right?

Compared with formatting using NumberToStringEx which is rounding properly, wouldn't it make sense to not use ROUNDP?

[MarenC]

Compared with formatting using NumberToStringEx which is rounding properly, wouldn't it make sense to not use ROUNDP?

That would surely depend on the specifics, wouldn't it? Or did someone make you the rounding overlord?

I would say though, that rounding down in a financial solution, where the accounting principal of conservatism can hold sway, does seem less than ideal. Though at the same time, this may come into conflict with another accounting principal, materiality!

[qml]

I have figured out where the value 4283.77490234375 quoted in the original post comes from. This would be the most accurate 32-bit float representation. However, as I have already mentioned in my first reply, TM1 uses 64-bit (double-precision) encoding, for which the most accurate value representing 4283.775 is the equivalent of 4283.7749999999996. So your colleague was mistaken in that regard. And yes, the real value will still be rounded down when using ROUNDP, but... so would an exact value of 4283.775.

[wbf]

Compared with formatting using NumberToStringEx which is rounding properly, wouldn't it make sense to not use ROUNDP?

That would surely depend on the specifics, wouldn't it? Or did someone make you the rounding overlord?

The format requested is two decimal. Given this real number 4283.775, may I ask what additional question would you ask to get your "specifics"? Would it be "would you want .77 or .78? "

but... so would an exact value of 4283.775.

Thanks! I do understand how TM1 is storing the value in float and hence why .775 was rounded to .77 if ROUNDP(x,2) was used. But regardless it's 4283.77490234375 or 4283.7749999999996, generally when given the number 4283.775 and again if the format expected is two decimal, I strongly believe that no one would say .77 is correct, right?

[qml]

generally when given the number 4283.775 and again if the format expected is two decimal, I strongly believe that no one would say .77 is correct, right?

Well that's the crux right there, isn't it? There isn't one "correct" approach to rounding. It really depends on what MarenC called "specifics" and there are a variety of ways of rounding devised, all equally valid by themselves.

Below is an excerpt from the description of the TM1 ROUND function, which specifically refers to rounding to full integers, but the same ideas can be generalised to rounding at any decimal precision. And the methods listed are just the tip of the iceberg! The Wikipedia article on rounding is significantly over 7 thousand words long and a fascinating read.

The most basic form of rounding is to replace an arbitrary number by an integer. There are many ways of rounding a number y to an integer q.

The most common ones are:

Round to nearest
q is the integer that is closest to y (see "Round away from zero" for tie-breaking rules).

Round towards zero (or truncate)
q is the integer part of y, without its fraction digits.

Round down (or take the floor)
q is the largest integer that does not exceed y.

Round up (or take the ceiling)
q is the smallest integer that is not less than y.

Round away from zero
If y is an integer, q is y; else q is the integer that is closest to 0 and is such that y is between 0 and q.

TurboIntegrator essentially uses the Round down method of floor(x + .5). Microsoft Excel uses the Round to nearest method. This can result in different integers depending on whether you are using a TurboIntegrator process or working in Excel.