Am I going crazy? Why is Excel Telling me different numbers are the same?!?!?!

[jimmy90]

Excel 2007

	A	B
1	92458901135721576158003019	4
2	92458901135721576158001015
3	92458901135721576162001018
4	92458901135721576162002015

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =COUNTIF(A1:A4,A1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Also, If I highlight Column A and use the built in conditional formatting highlight duplicate values all values are highlighted.

I have tried converting the column to Text and no Number. Nothing works.

Does this have something to do with the Scientific Notation? Even though the numbers do not appear as scientific notion and Excel documentation on Notation states that it does not change the cells actual value, I have no other ideas.

 

[Jonmo1]

Excel only calculates to 15 significant digits.

Number of significant digits MS Excel retains

 

[RoryA]

Try using SUMPRODUCT instead:
=SUMPRODUCT((A1:A4=A1)+0)

Unlike COUNTIF, SUMPRODUCT will not try and convert the criteria to numbers if possible.

 

[jimmy90]

Excel only calculates to 15 significant digits.

Number of significant digits MS Excel retains

So, is there a work around? Or am I just SOL