Excel bug

Discussion in 'Mac Apps and Mac App Store' started by oli2140, May 12, 2011.

  1. oli2140 macrumors 6502

    Jan 13, 2008
    Hi all,

    Five brownie points to anyone that can work out a solution to this excel bug!

    So to replicate try the following:

    In cell A1 type 12345
    In cell A2 enter the formula =right(A1,5)
    In cell A3 enter the formula =if(A1=A2,"True", "False")

    You should see (incorrectly) false.
    Now copy A2 and Paste special as values over A2 - still doesn't recognise that the two cells have 12345 in :mad:
  2. GGJstudios macrumors Westmere


    May 16, 2008
    Using =EXACT(A1,A2) in place of your =if(A1=A2,"True", "False") returns True.
  3. Phil A., May 12, 2011
    Last edited: May 12, 2011

    Phil A. Moderator

    Phil A.

    Staff Member

    Apr 2, 2006
    Shropshire, UK
    It's not a bug: You are storing a number in the first cell and a string in the second (generated when you did the right() call), so they aren't the same and the result of the comparison should be false

    You can either use GGJStudio's suggestion, or amend your formula to force the first value to be a string:

  4. Raid macrumors 68020


    Feb 18, 2003
    This is actually correct, the value reproduced in A2 returns a string, and a value (to use the excel syntax) <> string.

    If you want the if statement to return "True" then either put a value() function around the formula in A2 or use =if(A1=value(A2),"True","False") in A3

    Now, where are my brownies? ;)

    <edit> Phil A. Your solution hurts my soul... you could have used a text() function instead of concatenating a "" to make it a string.... didn't even use concatentate().... </edit>
  5. GGJstudios macrumors Westmere


    May 16, 2008
    This is exactly right. If, after you paste values, you change the formats of the cells to match, either both text or both number, the result of =if(A1=A2,"True","False") will return True.

Share This Page