decimal maths vs binary equivalent

[stuck]

I have a calculation in my code that in real world / decimal maths equates to:
    (18.55 x 10) - 185 = 0.5
However in the binary / hex / whatever that Excel is working in the answer does not come out at exactly 0.5. Debug.Print tells me it works out at:
    0.499999999999972

So far so simple. Except Debug.Print must be lying because if I do a logical test based on the number I get from Debug.Print:
    If (variable1 x variable 2) - variable 3 >= 0.499999999999972 Then
        do this line
    End If
The test does NOT return TRUE, i.e. the exact same calculation that Debug.Print tells me is 0.4...72 does NOT actually equal 0.4...72 so the code doesn't do the line it is supposed to. I have to tweak the number I get from Debug.Print, down to:
    0.499999999999971
before the test is TRUE.



Any comments anybody?

Ken

[HansV]

Debug.Print is VBA, not Excel. In Excel itself:

S1208.png

However, both VBA and Excel do not return "correct" results in all circumstances because of the way they handle numbers. See Floating-point arithmetic may give inaccurate results in Excel.

It's best to round results to the appropriate number of decimal places. In your example, the numbers 18.55, 10 and 185 have at most 2 decimal places, so you could use

Debug.Print Round((18.55*10)-185,2)

[stuck]

The line of code in question is part of a user defined function (originally written in the mid 1990s by my then line manager but who is now long retired) that rounds a value to a given number of significant figures. Today someone noticed that a value of '18.55' was not rounding as we expected, to '18.6', but instead was coming out as '18.5'. As I set out above, this was because a logical test in the code that superficially should have been TRUE, wasn't.

For now I'm going to go with using the tweaked value in the logical test but I suspect it will fall over again for a different case. Hopefully by the time that one crawls out of the woodwork I'll be fully retired. Making it a SEP (someone else's problem)

Ken

[SpeakEasy]

You can always coerce VBA to work internally with the decimal data type

[Jay Freedman]

Today someone noticed that a value of '18.55' was not rounding as we expected, to '18.6', but instead was coming out as '18.5'.

As pointed out in the Round function documentation,

This VBA function returns something commonly referred to as bankers rounding. So be careful before using this function. For more predictable results, use Worksheet Round functions in Excel VBA.

And the Banker's Rounding article explains that

Under Banker’s rounding, such ambiguity is resolved by rounding to the nearest rounded value such that the least-significant digit is even.

For example, when using Banker’s rounding to round to the nearest 1, both 73.5 and 74.5 round to 74, while 75.5 and 76.5 round to 76.

[stuck]

This VBA function returns something commonly referred to as bankers rounding...

Sorry, my wording wasn't clear. I know about VBA's round function using banker's rounding (unlike the spreadsheet function that always rounds '5' up). For that reason, the code in the UDF doesn't use either native VBA rounding or rounding via application.spreadsheetfunction. The problem I came across is, as Hans pointed out, to do with floating-point arithmetic not calculating the correct answer.

Ken

[SpeakEasy]

I repeat, try working in Decimal. In your first example change

(18.55 x 10) - 185

To
(CDec(18.55) x 10) - 185

Without going in to the mechanics of this, this will cause VBA to do the whole calculation in Decimal

[stuck]

Apologies for the delay in responding, got sucked into Other Stuff^TM

Yes, using CDec works and means I can use 0.5 in my my IF statement, rather than a messy 0.4999.... approximation.

I never knew CDec was a thing

Ken