Search code examples
excelhexcountif

Strange behavior of CountIf() on hex values

Environment: Excel 2021 under Windows 11 Pro 64.

We know that Excel's CountIf() has the nasty behavior of converting text that looks like a number to a number before counting. But now, it appears that it doesn't even do those conversions correctly on hex values.

Consider the following spreadsheet:

Decimal Hex Hex 4 Count decimal Count hex Count hex 4 Note
Manual =DEC2HEX(A4) =DEC2HEX(A4, 4) =COUNTIFS(A:A, A4) =COUNTIFS(B:B, B4) =COUNTIFS(C:C, C4) Manual
0 0 0000 1 1 3
1 1 0001 1 2 2
224 E0 00E0 1 1 3 Treating 0^0 as 0
225 E1 00E1 1 1 3 0^1 = 0
481 1E1 01E1 1 2 2 1^0 = 1
482 1E2 01E2 1 2 2 1^2 = 1
483 1E3 01E3 1 1 1 1^3 = 1
7680 1E00 1E00 1 2 2 1^0 = 1
7681 1E01 1E01 1 2 2 1^1 = 1
7682 1E02 1E02 1 2 2

We can see that:

  • Correct: The 1 in column "Count hex" for 0 indicates that nothing else in column "Hex" is being treated as equal to zero, which is correct.
  • Correct: The two 1s in column "Count hex" for E0 and E1 indicate that they are not treated as equivalent to numbers, which is correct.
  • Correct and debatable: The three 3s in column "Count hex 4" indicate that Excel is treating 0000, 00E0, and 00E1 as equal to each other. This is correct for 0000 and 00E1, which are both equal to zero. It's debatable for 00E0, which is ambiguous, but usually treated as equal to 1.
  • Wrong: The 2 in each of columns "Count hex" and "Count hex 4" for 1 and 0001 indicate that those values are each treated as equal to one other value in their columns, which is wrong because there are many other values in both columns that are equal to 1.
  • Wrong: The 1 in each of columns "Count hex" and "Count hex 4" for 1E3 and 01E3 indicate that those values are each treated as NOT equal to any other value in their columns, which is wrong because they are both equal to 1 and therefore equal to many other values in both columns.
  • Wrong: The other ten 2s in the table make no sense because they are all for hex values that are equal to 1. They should all be 7s.

Is this another bug in Excel, or is there some reasonable explanation for this behavior?

Solution

  • Everything here is working as expected. The confusion comes over the exponential notation: if x and y are numbers, xEy means "x multiplied by 10 to the power y" - not "x to the power y".

    For example, 1E2 means "1 times 10^2" = 1 * 100 = 100, while 0E0 means, unambiguously, "0 multiplied by 10^0", which is 0 (because 10^0 is well defined as being 1).

    So Excel's implicit conversions of your hex values to numbers are as follows:

    • E0 / E1 - invalid. (These will be treated as text by COUNTIFS().)
    • 1E1, 1E2, 1E3 are 10, 1000 and 1000 respectively.
    • 1E00, 1E01 and 1E02 are 1, 10 and 100 respectively.
    • 00E0 and 00E1 are both 0 (respectively, 0 * 10^0 and 0 * 10^1).
    • 01E1, 01E2 and 01E3 are identical to 1E1, 1E2 and 1E3 (10, 100, 1000).
    • 1E00, 1E01 and 1E02 are identical to 1E0, 1E1 and 1E2 (1, 10, 100).

    To see the implicit conversions in Excel, try adding two new columns which apply the operation *1 to your Hex and Hex 4 columns. You will see exactly the numerical values to which Excel converts them.

    Applying your COUNTIFS() formula on the converted columns will be identical to the results in your table, except that E0 and E1 will have become #VALUE! errors and will therefore be treated as equal by COUNTIFS().