You may not have heard about the so-called “floating point error” in Excel, in which seemingly simple calculations can give an apparently wrong result. It can certainly be bewildering if you experience it for the first time. To be clear this is not an issue with Excel but a function of how some numbers are represented in binary format(1). For very long numbers, more than 15 digits, one common solution is to represent them as text, but what happens when the text solution also produces a floating point error?

Here’s the scenario. A user(2) had three columns of “numbers” (although stored as text) and wanted to compare each line and check for duplicates. To accomplish this they had concatenated together the three entries per row into a single long number, as shown in columns B:E in figure 1 below. Columns B:D are formatted as text, and the CONCATENATE formula [=CONCATENATE(D3,C3,B3)] in column E also returns text.

fptext1 Figure 1

fptext2 Figure 2

Then they set up conditional formatting to highlight any duplicates (Figure 2), but the results turned out to be pretty weird, because it highlighted entries that were clearly not duplicates (Figure 3). It can be seen from this picture that Excel thinks cells E7, E9, E13 and E14 are duplicates in the list. Changing one of the entries slightly, such as changing D7 to 70007445, will reveal that Excel thinks E7 and E13 are duplicates of each other, and [E9,E14] are also a duplicate pair. But they’re not - in both cases one of the pair ends in 1080, and the other ends in 1084. To double check, I added the simple formula in column G to display TRUE if the value is duplicated and leave a blank if not, which corroborated the result conditional formatting was giving (Figure 4).

fptext3 Figure 3

fptext4 Figure 4

So what’s going on? The only thing I could think was that despite the values being text, the lack of any alphanumeric characters in the string meant they were being “seen” as numbers. Excel can only store numbers to 15 digits of precision, so in effect, in the case of [E7,E13], the 1080 and 1084 became 1000 and the entire number became 700074465004371000 in both cases.

The solution then is to force the number to be treated as text, by adding an alphanumeric somewhere, most easily at the beginning or end. And sure enough, adding the letter “a” to the end of every string by adding it into the CONCATENATE statement [=CONCATENATE(D3,C3,B3,“a”)] fixes the problem (Figure 5).

fptext5 Figure 5

Comments or questions? You can leave a comment below.

(1)For a description of how Excel stores and calculates floating point numbers and how the issue may manifest itself, see this Microsoft article.

(2)View the original thread on the Excel community forum.