Excel bug

[oli2140]

[deleted]

 

[GGJstudios]

Using =EXACT(A1,A2) in place of your =if(A1=A2,"True", "False") returns True.

 

[Phil A.]

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")

 

[Raid]

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

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>

 

[GGJstudios]

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

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.