• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Format as Text workaround to 15 digits of precision problem fails for COUNTIF

jofjltncb6

New Member
(Long-time reader, first-time poster...)


As I'm sure many others have, I learned about the 15 digits of precision issue by experience...bad experience. Fortunately, it was nothing mission critical. When I realized what Excel was doing, I tried the obvious workaround of formatting as text, a solution I later noticed was published here 4/7/2009; however, despite the Excel warning me that it was a number stored as a text, Excel continued to evaluate the cell as if it were a number. As a result, COUNTIF considered 1234567890123456 to be the same as 1234567890123457, 1234567890123458, etc. Longer lengths were evaluated the same regardless of which digits were beyond the 15th place if the additional characters were digits and the lengths were same. 12345678901234567 is the same as 12345678901234568, but different from 123456789012345678. I could not find a way to force Excel to treat the cell contents as text.

[pre]
Code:
Number as text		Formula			Result
12345678901234		=COUNTIF($A$2:$A$9,A2)	1
12345678901235		=COUNTIF($A$2:$A$9,A3)	1
1234567890123456	=COUNTIF($A$2:$A$9,A4)	2
1234567890123457	=COUNTIF($A$2:$A$9,A5)	2
12345678901234567	=COUNTIF($A$2:$A$9,A6)	3
12345678901234568	=COUNTIF($A$2:$A$9,A7)	3
12345678901234569	=COUNTIF($A$2:$A$9,A8)	3
123456789012345678	=COUNTIF($A$2:$A$9,A9)	1
[/pre]

The workarounds I have tried so far using SUMPRODUCT fail as it considers two cells a match if one cell string contains the contents of another cell. (=SUMPRODUCT((LEN($A$2:$A$9)-LEN(SUBSTITUTE($A$2:$A$9,A2,"")))/LEN(A2)) is an example that almost works.)


Not necessarily looking for a solution. More curious if anyone else has seen this behavior from Excel (evaluating cell contents as numbers despite being explicitly formatted as text) or if I'm ignorantly overlooking something.
 
Have a read here for a discussion on working with Large numbers

http://tushar-mehta.com/misc_tutorials/project_euler/LargeNumberArithmetic.htm
 
Back
Top