Excel bug

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

Most Liked Posts
  1. oli2140, May 12, 2011

    oli2140 macrumors 6502

    Joined:
    Jan 13, 2008
    #1
    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:
     
    share
    + Quote Reply
  2. GGJstudios, May 12, 2011

    GGJstudios macrumors Westmere

    GGJstudios

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

    Phil A. Moderator

    Phil A.

    Staff Member

    Joined:
    Apr 2, 2006
    Location:
    Shropshire, UK
    #3
    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:

    =IF(""&A1=A2,"True","False")
     
    share
    + Quote Reply
  4. Raid, May 12, 2011

    Raid macrumors 68020

    Raid

    Joined:
    Feb 18, 2003
    Location:
    Toronto
    #4
    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>
     
    share
    + Quote Reply
  5. GGJstudios, May 12, 2011

    GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #5
    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
    + Quote Reply
(You must log in or sign up to post here.)

Share This Page