Rounding: VBA vs Worksheet

[JimDandy]

I'm trying to understand the variation between rounding a series of values via VBA and rounding within a worksheet. The VBA routine that performs the rounding should be using the same rounding rules as the worksheet at least that's what I thought given that the VBA command uses WorksheetFunction.RoundUp(cell.Value, 0), which I expected to mean that it uses the same calculation as the worksheet, i.e. =ROUNDUP(XLOOKUP($A$36,$A$20:$A$32,C20:C32),0).

Anyway, the attached spreadsheet has two tables in yellow highlight. The left table uses data selected from a row in a calculation table at the bottom, the right table uses data from an array in VBA using the exact same calculation.

Selections are made using Option Buttons. The left set of option buttons uses a macro that has variables set using As Single data type, the right set of option buttons uses the exact same macro but uses As Double data types. In both sets of buttons, I've highlighted the button whose results differ between the calculation table and VBA.

What I've noticed is that using As Double provides fewer mismatched results. The difference comes nearly 14 decimal places in however. For instance, the value "9.00000000000006" results in rounding up to 10 in VBA. In the spreadsheet itself, this results in a 9.

When using As Single however I've seen (using the Immediate Window and Debug.Print) that 12 with no decimals results in being rounded up to 13.

The questions are: Is there a way to ensure that both VBA and a table using the same formula can achieve the exact same results and how does 12 get rounded up to 13? In the attached spreadsheet if you watch the Immediate window and select the left hand Option Button named Growth 12 - 15 yr you can see where 12 becomes 13 when rounded up.

Rounding up.png

[MrShorty]

When I see a number like 9.000...0x, that suggests to me that the issue is floating point error. (More than you probably want to know about floating point errors: https://www.excelforum.com/groups/ma...nd-errors.html ). I expect that the difference between what you see in VBA and what you get when you use the same thing in Excel is that the spreadsheet attempts to implement a few "improvements" to try to detect and guess what it should do with floating point errors that are not implemented in VBA. Single and Double are both floating point data types, and I expect that any difference between Single and Double are a result of the different approximations (Single will sometimes approximate below actual and Double approximate higher).

Floating point errors are an inherent part of the way computers (not just spreadsheets) perform arithmetic, so we as programmers just have to figure out some way to deal with them. You have not shared any of your computation details that lead up to these values. A common first guess is to do some kind of double round. =ROUNDUP(ROUND(value,6),0) (The longest example number I see has 6 digits past the decimal point, so I chose 6). This first rounds to the nearest 1E-6 (so 9.00000000006 will round down to 9.000000). Then applies the ROUNDUP(). It's certainly not the only way, but it is one common way to do this.

Another approach might be to subtract a small amount from the value before rounding up. If your errors are always past the 7th digit past the decimal point, then something like ROUNDUP(value-1E-7,0) can work.

[Marc L]

(removed as I misread Round instead of RoundUp)

[Marc L]

A sample of the decimals accuracy according to the variables types :

Sub DemoCalculationPrecision() 
    Const F$ = "20.4 - 19.6", L$ = vbLf & vbLf 
    C@ = Evaluate(F) 
    D# = Evaluate(F) 
    S! = Evaluate(F) 
    MsgBox Space(27) & "Calcul  :  " & F & L & "Type :    (@)  Currency =  " & C & L & _ 
           Space(16) & "(#)     Double =  " & D & L & _ 
           Space(16) & "(!)        Single =  " & S, vbExclamation 
End Sub  

[Marc L]

When using As Single however I've seen (using the Immediate Window and Debug.Print) that 12 with no decimals results in being rounded up to 13.

Not on my side, the result of a single variable containing 12 with the ROUNDUP worksheet function is 12 …

[JimDandy]

MrShorty,

I appreciate the great explanation and I was able to eliminate the problem with your suggestion of double rounding both the VBA code and the spreadsheet. A few mismatched results remained when using As Single but that's not a requirement so I couldn't be happier with the results.

Thank you!