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?????

 

[Richard Schollar]

It's to do with Excel's limited precision and the way fractions are represented in binary. It is common to all current computing systems (although the degree of precision may vary between computer systems).


EDIT: see this link
http://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel

 

[shg]

If A1:A20 each contain 5%, the sum is not exactly 100%; it differs in the least significant bit. That's just a limitation of binary floating-point numbers.

But that difference is nowhere close to the 0.001 error that you're seeing -- there's another problem somewhere.

 

[Richard Schollar]

Oops Shg is right - the difference if due to floating point math shouldn't be that big.

 

[business_analyst]

It's to do with Excel's limited precision and the way fractions are represented in binary. It is common to all current computing systems (although the degree of precision may vary between computer systems).


EDIT: see this link
http://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel

Thanks Richard, I thought it was some issue involving the precision of Excel, but wanted to make sure this was true. A method that I took to circumvent this issue, was to not have my vba message prompt if the sum > 1, but instead I changed this to sum > 1.001. This is not precise, which is a negative, but it does work while providing only a faction of inaccuracy.

Would you by any chance have any other suggestions of how to address this issue? Possibly anything that would work while eliminating any discrepancies?

 

[business_analyst]

If A1:A20 each contain 5%, the sum is not exactly 100%; it differs in the least significant bit. That's just a limitation of binary floating-point numbers.

But that difference is nowhere close to the 0.001 error that you're seeing -- there's another problem somewhere.

Ooh.. well the exact number in the prompt box was: 9.99999999999668 E-04.. does that make a difference?

 

[business_analyst]

Ok so upon further investigation:

1. In my check, I was actually subtracting by 1.001, not by 1, which would explain the .0009999 answer. When i switched this to 1 as it should be.. the message box prompted with: -2.2204604925031 E-16... which looks more like the tiny difference you guys are talking about.

2. I think I found a way around this issue by setting Excel calculations to "Precision as Displayed"

 

[Richard Schollar]

That's quite a dangerous setting since precision is permanently lost (ie you will only ever have data to the precision of the display) - this may or may not be a problem for you. You could instead have simply rounded your values using the Round VBA function (eg set it to 2 dp to check):

If Round(Cells(1,1),2)>1 Then ...

 

[shg]

You might not want to do that -- it permanently changes every data entry to exactly what you see.

Perhaps instead

If Abs(1-Sheet15.Cells(1, 1).Value) > 1e-10 Then

 

[business_analyst]

I think i will utilize that method rather than the precision as displayed option. Thanks for helping me better understand this issue and how to approach it. I'm sure I'll look really smart sometime in the future when I'm explaining THIS to somebody else