All geek questions in one place

Error in Excel function

I'm having problems with the CountIf function in Excel.

=COUNTIF(A:A,A2)

Column A consists of the following elements:

 0107791489614255200011140926107503100513 0107791489614255200011140926107503100457 0107791489614255200011140926107503100518 0107791489614255200011140926107503100503 0107791489614255200011140926107503100519 0107791489614255200011140926107503100444 0107791489614255200011140926107503100521 0107791489614255200011140926107503100438 0107791489614255200011140926107503100449 0107791489614255200011140926107503100443 0107791489614255200011140926107503100501 0107791489614255200011140926107503100455

the result is up to 12, although these lines are not exactly the same. He considers these lines to be similar lines, I think it is related to the length of the line?

What do you guys think? I appreciate your help.

+5
source share
2 answers

The preceding line with one apostrophe will not correct the situation. COUNTIF is designed to interpret data as numeric, where possible, regardless of the data type of the corresponding values. Sometimes it’s useful, sometimes (like here) not.

SUMPRODUCT does not have this property:

 =SUMPRODUCT(0+($A$1:$A$12=A2))

will return 1 if required.

Hi

+3
source

+1, Good question. Not really a mistake, but a function!

This is due to the fact that Excel implicitly converts the input data to its internal numeric type and loses accuracy in doing so. Excel's internal numeric type is an IEEE double-precision floating-point number. (Although he does smart things with formatting and propagating errors, so he seems to get amounts, like 1/3 + 1/3 + 1/3).

Because they are so similar, they are all compared as mutually equal.

One way would be to prefix each string with a ' (single quote), which will prevent conversion to a numeric type. Then the COUNTIF value returns 1. (At least in my version of Excel, 2013).

+4
source

Source: https://habr.com/ru/post/1206699/

More articles:


All Articles