WorksheetFunction.Min Returning Inaccurate Values from Array Varriable (Excel Bug?)

[Katelynne]

I have inaccurate results being returned from the Min( ) and Max( ) functions when used in VBA script. If the value being returned from the array is a whole number, these functions are returning the correct value. If not a whole number, then these functions are returning the correct-ish value, but with digit places well beyond what is present in the data, and not all zeros. The script I'm using is generating an array which I am then inputting into the Min() or Max() function. While testing, I have even tried forcing the data in the array using the Immediate window just in case the data being pulled in the table is being displayed by Excel after rounding. The provided values are max 3 digits past the decimal, but the return is 14 digits past with not all being 0's.

Excel version: O365

Example Data

Site . . . . . . BW
101 . . . . . . 3
102 . . . . . . 0.512
103 . . . . . . 1.544
103 . . . . . . 0.768
104 . . . . . . 6

Script exerpt:

Dim site as String
Dim DataCkt As Range
Dim BW() As Single
Dim MinBwdth as String


site=103
Set DataCkt = Sheet1.Range ("A2:A6") '<--The data in the above sample
SIDcount = 0

'This part of my script works without issue returning the data from the appropriate matched lines in the range.
For Each entry In DataCkt
    If entry.Value = site Then 
        RowNo = entry.Row '<--Identify the row
        SIDcount = SIDcount + 1 '<--Advance the counter
        ReDim Preserve BW(0 To SIDcount - 1) '<--Add an additional array entry while preserving the current array data
        BW(SIDcount - 1) = Sheet1.Cells(RowNo, 2).Value '<--Set the new array entry to the value
    End If
Next entry

'Checking the values in the array
Debug.Print BW(0)
  1.544
Debug.Print BW(1)
  0.768

'This part is where I'm getting the inaccurate values returned.
Debug.Print WorksheetFunction.Min(BW)  '<--Same results for WorksheetFunction.Min(BW(0), BW(1)) with expected result 0.768
     0.76800000667572
Debug.Print WorksheetFunction.Max(BW)
      1.5440000295639

 

[Domenic]

Have a look a the following article, which explains the cause of precision errors, and offers a solution to the problem.

Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

Discusses that floating-point arithmetic may give inaccurate results in Excel.

learn.microsoft.com

Hope this helps!

 

[Wookiee]

Depending on whether you need that precision, if you want to just see the first 3 digits past the decimal, you could wrap your Debug.Print in Format( ... , "0.000")

 

[Katelynne]

Depending on whether you need that precision, if you want to just see the first 3 digits past the decimal, you could wrap your Debug.Print in Format( ... , "0.000")

The digits beyond 0.000 are extraneous added by Excel and don't belong as they aren't in the original data. Format(..., "0.000") would be fine, if I always wanted those placeholders (as with currency always showing the cents). As you can see from the other sites in the sample data, not all returns would have digits past the decimal. If I had called on site 101, then I would expect the return to be 3 and not 3.000. As it is, my script does work as-is with whole numbers. If the array was BW(3) or BW(3, 6, 10), then Min(BW) returns 3 instead of 3.0000000233459. Or do I need to put an if-then statement in to handle the sites with circuit bandwidths of T1 (1.544M) or less so those sites are rounded off to 0.000 format?

 

[Domenic]

As the article suggests, simply round to 5 decimal places, for example...

Debug.Print Round(WorksheetFunction.Min(BW), 5)

Hope this helps!

 

[Katelynne]

As the article suggests, simply round to 5 decimal places, for example...

Debug.Print Round(WorksheetFunction.Min(BW), 5)

Hope this helps!

Thanks. This seems to be working and better than the aforementioned Format( ..., "0.000") method. The whole numbers are remaining whole numbers while the results that have decimal parts are returning back to their original 0.000. This still shouldn't be necessary as no math was performed to change the original data, but it does seem to be a viable workaround the bug affecting Min() and Max().

 

[RoryA]

Excel stores numbers as Double and. you will often get this kind of issue if you use Single variables. Declaring your array as Double should also correct the output here.

 

[Domenic]

As @RoryA has already mentioned, you should declare your array as Double, instead of Single.

Also, I would suggest that you mark @RoryA's post as the solution so that others can benefit from it.