+ Reply to Thread
Results 1 to 5 of 5

If statement won't recognize two values as equal

  1. #1
    Registered User
    Join Date
    04-20-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    2

    If statement won't recognize two values as equal

    Hi all,

    I've included a detailed screen shot with some notes on it, I would encourage taking a look at that.
    The issue I'm having is that a loop statement of mine is failing to recognize that a couple values are equal. It works for the first 3 iterations of the loop, then stops recognizing. I tried copying another set of numbers in the failing one's place, and that actually worked.
    Not sure how else to explain it, but if you take a look at the screen shot you can see the excel sheet being worked on and the vba code running. I will also include a text file of the code for anyone who would like to change the code to offer a suggestion.

    Thank you!
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,743

    Re: If statement won't recognize two values as equal

    It's hard to say for sure, but this sort of thing usually turns out to be some kind of round off error. Either there's formatting or a round function that is hiding some of the digits, or it is simple round off error out in the 10+ digit due to errors in double precision arithmetic.

    Because round off errors are so common in any computer programming, my mentors almost never tested for "exactly equal to" like this. They would almost always do something like "is abs(a-b)<e" where e is some small number.

  3. #3
    Registered User
    Join Date
    04-20-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: If statement won't recognize two values as equal

    Hey,

    Thank you for the reply!
    I got another reply on another forum a few moments ago, and that was exactly the issue.
    The problem was corrected by changing the If statement to something like If Round(myVariable,2) = Round(myOtherVar) Then...

    So thank you for your reply!

  4. #4
    Valued Forum Contributor StevenM's Avatar
    Join Date
    03-23-2008
    Location
    New Lenox, IL USA
    MS-Off Ver
    2007
    Posts
    910

    Re: If statement won't recognize two values as equal

    This is a common problem with floating-point arithmetic. Do a Google search on this issue and you will find a lot written on this problem.

  5. #5
    Valued Forum Contributor StevenM's Avatar
    Join Date
    03-23-2008
    Location
    New Lenox, IL USA
    MS-Off Ver
    2007
    Posts
    910

    Re: If statement won't recognize two values as equal

    P.S. See: "Floating-point arithmetic may give inaccurate results in Excel" at: http://support.microsoft.com/kb/78113

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1