Rounding To 16Dp Decimal (3 Different Answers)

[dalski]

Simple math: 1/1.95:

In Excel, formatting cell to 16 dp & counting on screen the dp's I get

[td width="285px"]

0.5128205128205130​

[/td]​

Online Calculator I get
0.5128205128205128

In Sql Server using

SELECT
CAST(ROUND(1/1.95,16) AS DECIMAL (28,16)) AS WTF

I get
0.5128200000000000

I'm lost for words .

 

[MarkK]

Like almost everything, not really as simple as it may first appear.

• It is impossible to represent an infinitely repeating decimal with a computer. There is not enough memory to store infinity. It will be rounded or truncated or something, because the precise result is impossible.
• Because it is not precise, how each computer handles this imprecision could be different.
• There are different rules you might use to round a number. Ask your AI, about 'Bankers Rounding,' which is what the VBA.Round() function does.
• In banker's rounding, numbers are sometimes rounded up, sometimes rounded down. This prevents a bias in pennies that would accumulate if $0.005 always rounds up to $0.01

 

[dalski]

Thanks Mark, I'm a little familiar with BR, but I thought the whole point of using decimal is that it's precision is guaranteed. I thought SQL Server would use BR but this suggests it does not. Obv's you'll know a lot more than me. There must be a way to disregard client computers... because by that logic you wouldn't have Stock Exchange/ Online Banking/ Excel...

A separate topic - I'm avoiding overflow by specifying the rounding & also replicating the method in Excel to avoid memory truncation caused by floating point arithmetic in calc's by specifically specifying the decimal places of accuracy.

 

[isladogs]

Adding to the above, I'm surprised you didn't test in both Access and the Windows Calculator.
FWIW, both give the same result as the online calculator.
Does that mean those are the 'correct' answers? See post #2

 

[RonPaii]

I was able to get close to the SQL server answer by casing 1.95 to single. Could be you need to cast 1.95 to double before the calculation.

?1/csng(1.95)
 0.5128205

 

[DickyP]

The perfect reason for using a decimal data type in your dbms, and one of the reasons Access needs a native decimal data type. You will always get a consistent answer (even if it isn't perfect).

 

[dalski]

Thanks Colin & Ron.

• Windows Calc gives me the same as the website
• VBA gives me same as Excel; though I won't be using Access a great deal as most of the work will be done in SQL Server. Access will just be displaying data (to less decimal places & with less accuracy) but that's fine. The BE is doing the operations.

Sub blah()
  Dim dec As Variant
  Debug.Print CDec(Round(1 / 1.95, 16))
End Sub

FWIW, both give the same result as the online calculator.
Does that mean those are the 'correct' answers? See post #2Well it's not relating to BR because Excel does not use BR, whereas

In my feeble mind that seems worse - as it contradicts BR. I don't know what the right answer is, I'd assume the online calculator would be the most reliable as we're not seeing great consistency elsewhere but no doubt I'm wrong.

Could be you need to cast 1.95 to double before the calculation.

I must be missing something here, as I have chosen decimal because of it's 'guaranteed' precision & one would think sticking with the same datatype would be more robust. Single is wonderful in this instance but I'll be working with plenty bigger/ more precise numbers than single can handle, nor should I have to convert data type, decimal is the most accurate & it should be able to handle this.

Like I say to avoid it in Excel you need to explicitly round to the dp's required in every area. That's fine, it works in Excel. But it doesn't seem to affect SQL Server.

I need the math to be correct. Imagine Stocks & Shares, they'll be accurate to many, many decimal places. This rounding is costing me 4p on a primary calculation. With compounding it will add up to a lot. Moreso it needs to be correct. I've chosen a high 16 dp precision to mitigate this.

Interesting though isn't it.

 

[RonPaii]

I must be missing something here, as I have chosen decimal because of it's 'guaranteed' precision & one would think sticking with the same datatype would be more robust. Single is wonderful in this instance but I'll be working with plenty bigger/ more precise numbers than single can handle, nor should I have to convert data type, decimal is the most accurate & it should be able to handle this.

I converted 1.95 to single as an example to get the same result as you are seeing in SQL Server. My guess is that SQL Server is doing the calculation in the lower precision because the 2 terms can be represented as Integer and single. By casting 1.95 to double, you force SQL Server to do the calculation as a double.

 

[GPGeorge]

I converted 1.95 to single as an example to get the same result as you are seeing in SQL Server. My guess is that SQL Server is doing the calculation in the lower precision because the 2 terms can be represented as Integer and single. By casting 1.95 to double, you force SQL Server to do the calculation as a double.

Yup,

 

[MarkK]

omg, wtf?

 

[isladogs]

In the Access immediate window

?1/1.95
 0.512820512820513

?CDec(Round(1/1.95,16))
 0.512820512820513

?Round(CDec(1/1.95),16)
 0.512820512820513

In a textbox control with record source =1/1.95 and no formatting

 

[The_Doc_Man]

CAST(ROUND(1/1.95,16) AS DECIMAL (28,16)) AS WTF

The damage is done quickly. Look at 1/1.95 (as this appears in your initial post) and look at GPGeorge's contribution in post #9.

That CAST statement is SQL-Server, right? I believe 1/1.95 will compute as SINGLE(1)/SINGLE(1.95) because that is the smallest number size that will fit the two operands on either side of the "/" operator. The "1" could be an INTEGER, but 1.95 must be at least a SINGLE, so LET-coercion makes the "1" into a SINGLE as well. When you get 0.5128200000000000, you got that because SINGLE division gives you six or seven digits at most i.e. 0.512820. The other computations show that the 6th digit is legitimately a 0 and the seventh digit must have been lost to SINGLE mantissa-truncation.

Your "CAST ROUND(...) AS DECIMAL" is done after the SINGLE division has already occurred and the inevitable truncation has ALSO already occurred. Note also that starting with a longer possible mantissa could occur ONLY if your constant expression started out with more digits or contained the implicit type-marker (in VBA, it is "#", the octothorpe) that would signal it to be a DOUBLE. As far as I recall, LET-coercion cannot promote beyond DOUBLE. You MUST do an explicit typecast or function-based conversion to get to DECIMAL.