Random Functions Returning Maximum Digits Without Being Rounded???

Question

Hi everyone,

Yesterday was surprised as I came across some result, not exact enough, despite of using Decimal type data, using Excel DNA with C#.

Always had been of the opinion of Decimal returning Exact values.

Well, I was of the correct knowledge, but it took some time to return back to the said belief.

A combination of functions like:

=RANDBETWEEN(1,100000)+RAND()

returned a value like

53232.5630803013

on the Excel Interface, while using a C# code like the following to determine the fraction part separately:

Math.Round((OriginalNumber - IntegerPart) * (decimal)Math.Pow(10, LengthOfDecimalPlaces), 0);

The result turned out to be

5630803014 instead of 5630803013

It was during debugging that I found that a variable possessing the number, appearing as 53232.5630803013 on Excel was actually 53232.563080301348 i.e. 12 digit fraction (Additional 2 digits).

Thus, 5630803014 appearing because of being rounded up.

I had the said value being copied and pasted as special values.

Then recalled the fact of the Excel not possessing more than 15 digits.

Any reasonable justification of not generating core random figures within the said limits of 15 Digits or at least being rounded-off by default???

Answer 1

Disclaimer: I am not familiar with Excel-DNA, a third-party application, I believe. And I have not been able to find documentation about its Decimal type. So I cannot comment on its internal representation and limitations.

---

It was during debugging that I found that a variable possessing the number, appearing as 53232.5630803013 on Excel was actually 53232.563080301348 i.e. 12 digit fraction (Additional 2 digits).

[....]

Then recalled the fact of the Excel not possessing more than 15 digits. Any reasonable justification of not generating core random figures within the said limits of 15 Digits or at least being rounded-off by default???

Excel "processing" (calculation) is not limited 15 significant digits (except as noted below).

And Excel does not "store" only 15 signficant digits, contrary to what even MSFT documentation says.

But it is true that when converting numeric text, including what we enter manually, Excel interprets only the first 15 significant digits, ignoring any digits to the right. So Excel effective truncates, not rounds, data entry after 15 significant digits.

And it is true that Excel formats only up to 15 significant digits, rounding any digits to the right.

OTOH, Excel formats up to 17 significant digits when writing to XML files, including the worksheet files in "xlsx" and "xlsm" file, which are actually zipped archives.

And Excel interprets up to 17 significant digits when reading numeric text from XML files.

(Also, apparently the 17-significant-digit limits apply to interfacing between some applications, e.g. Access, AFAIK based on answering user's questions.)

FYI, Excel VBA also interprets 17 significant digits (and even more) when converting numeric text. However, like Excel, VBA formats only up to 15 significant digits.

However, even though 17 significant digits is necessary and sufficient to convert between decimal and binary representations with no loss of precision, according to the IEEE 754 standard, the 17-significant-digit value is still just an estimate of the exact value.

For example, what appears to be 53232.563080301348 is actually exactly 53232.563080301348236389458179473876953125.

---

Like most applications, Excel stores decimal numbers as the sum of 53 consecutive powers of 2, in accordance with the 64-bit binary floating-point standard (IEEE 754).

Since most decimal fractions cannot be represented exactly as the sum of 53 (and even more) consecutive powers of 2, their internal binary representation is an approximation.

(That also applies to most integers that exceed 2^53 (9007199254740992, which Excel displays 9007199254740990.)

Moreover, the binary approximation of a particular decimal fraction might vary depending on the magnitude of the number because some of the 53 consecutive powers of 2 are used to represent the integer part, leaving fewer powers of two and a less precise sum for the decimal fraction.

That is why, for example, 10.01 - 10 = 0.01 returns FALSE (!). The following shows the exact decimal representation of the binary approximation of the values on the left.

10.01 10.0099999999999997868371792719699442386627197265625

10.01 - 10 0.0099999999999997868371792719699442386627197265625

0.01 0.01000000000000000020816681711721685132943093776702880859375

In that case, the difference between 10.01 - 10 and 0.01 is apparent if we format the results with 17 decimal places.

But it is not always apparent, due to the Excel formatting limitation. For example:

A1: 33.03000000000000000 33.030000000000001136868377216160297393798828125
A2: 11.83000000000000000 11.8300000000000000710542735760100185871124267578125
A3=A1-A2: 21.20000000000000000 21.2000000000000028421709430404007434844970703125
A4: 21.20000000000000000 21.199999999999999289457264239899814128875732421875

In that case, A3=A4 returns TRUE because for the comparison operators ("=", "<>", etc), Excel rounds each operand to 15 significant digits internally just for the purpose of the comparison.

But ISNUMBER(MATCH(A3,A4,0)) returns FALSE because their binary values are different. Also, A3-A4=0 returns FALSE for a similar reason.

Such inconsistencies are due to dubious arbitrary design decisions that are specific to Excel (and work-alike apps). They are not part of the IEEE 754 standard. The dubious Excel behaviors were introduced in Excel 97 (circa 1997) in a half-baked attempt to hide some 64BFP arithmetic anomalies.

---

As for "any reasonable justification" for choosing 64BFP and for limiting input and formatting to 15 significant digits, the explanation is historical.

Answer 2

Faraz, you seem to be satisfied with my explanation of 64BFP representation.

But I am still confused by your description of the "real" problem, which seems to be about the C# type Decimal data type, not 64BFP.

Can you clarify?

---

First, I have done some "research" and learned that C# type Decimal is the same as VBA type Decimal, which I understand very well.

Nevertheless, I am not clear about the sequence of events that you describe, namely: what was calculated in to Excel; what was calculated in C#; and what language displayed 5630803014 instead of 5630803013?

---

I think you are saying that you did the following. Please correct any misunderstandings. Better yet: provide a complete step-by-step description of your own that is similar to the following.

1. In Excel, you calculated =RANDBETWEEN(1,100000)+RAND(), which displayed 53232.5630803013 (15 sig digits), but which you discovered later is more precisely approximated by 53232.563080301348 (17 sig digits).

And as I explained, the exact decimal value is 53232.563080301348236389458179473876953125.

Aside.... We can reproduce that exact binary value with the expression 53232.5630803013+"5.09E-11".

2. Using Excel-DNA, you stored the Excel type Double result into the C# type Decimal variable OriginalNumber, and "extracted" the integer part (53232) into the type Decimal variable IntegerPart.

Please provide the complete C# code.

3. Then using Excel-DNA, you "extracted" the decimal fraction and converted it to an integer with the following C# statement:

Math.Round((OriginalNumber - IntegerPart) * (decimal)Math.Pow(10, LengthOfDecimalPlaces), 0);

where LengthOfDecimalPlaces is presumably 10.

4. Somewhere, the result was displayed as 5630803014 instead of 5630803013.

Where did you see that result: in Excel; or in C# using Excel-DNA?

And what operation displayed that value?

5. You concluded that the fractional part of type Decimal 53232.563080301348 or 53232.563080301348236389458179 was rounded up, not rounded, to a 10-digit integer.

In which step?

---

I would be surprised if C# rounds up. Excel and Excel VBA do not.

Answer 3

Our commercial computers are based on a binary system. Therefore, any floating-point number is imprecise, no matter how many decimal places it has.

Only mathematical expressions like 2/3 are exact.

The precision of the data type for a floating-point number depends on all digits, not just the digits after the decimal point. This is logical since we need to store all the numbers. But that doesn't mean that we can only store numbers with 15 digits.

A1: -30
A2: =A1+1

B1: =2/3*10^A1

Andreas.

Answer 4

Hi Faraz,

Greetings! Thank you for posting to Microsoft Community.

It looks the design behavior and should be done from the related teams. You can submit it to Microsoft:

Send feedback to Microsoft with the Feedback Hub app - Microsoft Support

The related team will check it every day.

Best wishes，

Snow Lu