Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Number match (Excel 2000)

    Hi Loungers, still working on the reconciliation. Another issue, I am using the following formula looking for matches: =ISNUMBER(MATCH(S2,$AO$2:$AO$2500,0))

    I have the following data in cell S2: 237.52162220049858 and this data is in cell Ao 7: 237.52162220049858. To me these are equal; however, Excel returns a false indicating inequality. Any ideas for a fix?

    All help appreciated.

    Mitch

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number match (Excel 2000)

    First, are those entered as numbers or text. Those two numbers have 17 significant digits and Excel has a limit of 15 significant digits (as does the hardware with double precision floating point). Therefore, I don't see how those could be entered as numbers. If they are entered as text, I don't see why they would not be considered equal.

    If you in fact do not have 17 digit numbers, then again I don't see why they would not be considered equal if they are as you posted them. If there is in fact a difference in the last few digits, then you need to round the numbers before comparing them. You could use some thing like the following arrar formula (you must hold down Shift+Ctrl when you press the Enter key to enter the formula):

    <pre>=ISNUMBER(MATCH(ROUND(S2,7),ROUND($AO$2:$A0$2 500,7),0))
    </pre>


    The formula above rounds the values to 7 digits after the decimal. You would need to change that to the number of digits that suits your purposes (usually the number of displayed digits).
    Legare Coleman

Posting Permissions

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