WorksheetFunction.Sum() Not Equal to Range().Value

[Nathan.Sproul]

Hello all,

I have this line of code:

If WorksheetFunction.Sum(Range("G4:G11")) <> Range("I4").Value Then

which is evaluating to true when I have these numbers:
G4 = 495.00
G5 = 221.18
I4 = 716.18

I ran this code to check that these were indeed equal:

        MsgBox Range("I4").Value
        MsgBox WorksheetFunction.Sum(Range("G4:G11"))

both of these MsgBoxes returned 716.18.

Anyone know why Excel doesn't think these are equal?

Thanks,
Nate

[MrShorty]

My first guess is that it is a rounding error (http://www.cpearson.com/Excel/rounding.htm ) I notice that =(I4-sum(G4:G5)) yields something like 1E-13 -- not exactly 0. Anytime you deal with arithmetic in a computer (this is not limited to Excel), you have the potential for round off error, and your programming needs to be prepared to handle it.

Experienced programmers that I know never test for "exactly equal to" or "not equal to" in the way you are doing it here. They will account for this round off error in some way:

Is abs(a-b)<epsilon where epsilon is some small value that represents the necessary precision.
Is round(a,epsilon)<>round(b,epsilon) where epsilon, again, represents the level of precision that the test is required to pass.

I expect that is the issue you are up against, and that you will need to adapt your code to account for this round off error.

[Nathan.Sproul]

Ok, yeah, I think you're right, I tried this:

If Round(WorksheetFunction.Sum(Range("G4:G11")), 2) <> Round(Range("I4").Value, 2) Then

and it seems to be working now. Thanks for the great article link too, makes much more sense now.