Possible Glitch found in MS EXCEL

[business_analyst]

Hello All,

I've come across an interesting quandary concerning calculations driven by excel worksheet formulas versus calculations driven by vba code. Time for everyone to put their thinking caps on!!

So I have a worksheet with a column in which I am entering percentage (the format of the cells have been set to percentage). At the top of this column is a SUM formula (also in percentage format), which sums all percentages entered within this column. What I wanted to include on the VBA end, was some code in the _SelectionChange method of the worksheet. This code theoretically would prompt me with a message box if the percentages I enter within this column exceed a total of 100% (the code reads the SUM cell in the first row of the column). Here is the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim continue As String
    
    Dim value As Double
    
        If Sheet15.Cells(1, 1).value > 1 Then
'            value = 1 - Sheet15.Cells(1,1).value
'            MsgBox Format(value)
            
            continue = MsgBox("Total percentage for this group cannot exceed 100%", vbOKOnly + vbExclamation, "Invalid Entry")
            Exit Sub
        End If
end sub

Now, here is where things get weird. So this code works fine, if I enter 25% four times, when I enter it the fifth time, I am prompted with this message box. HOWEVER, if, lets say, I enter 5% twenty times, that should equal 100% correct? I should be able to enter 5% twenty times and on the twenty-first time, it doesn't work. BUT this is untrue. For sum reason the VBA code prompts me on the twentieth 5% entry, meaning somehow it thinks that equals more than 100%....

Furthermore, the commented text in my code shows an attempted check I put it, where I subtract the SUM value in the first cell (which shows 100%) by 1. This should equal 0 right? Nope, it actually equals 9.9999 X 10^-4. Meaning its off by approx .0009999...

In conclusion, apparently, while the SUM formula on the sheet clearly sums to 100%, the vba code in _Selectionchange method of the sheet does not agree. WHY?????

 

[shg]

Just tell them, "Oh, yes, it's quite standard practice to compare floating-point numbers with a tolerance."