Unique entry count

[Montrose77]

Strange thing has happened with this forumula which was working fine i
my original document, but when I added a new column suddenly starte
acting up.

Formula is entered in cell CM2 as an array as follows:
{=SUM(IF(BF2:CF2<>"",1/COUNTIF(BF2:CF2,BF2:CF2)))}

The 'expected' result should be the number of unique entries in tha
range - and it worked as such in an earlier version of the document
However, since adding a new column to the range (yes, the start/en
columns updated correctly) it has developed an anomaly which means tha
if every cell in the range has the same entry, with no blanks, then th
result is 0.999999999999999000000000000000 (9's to the 15th decima
point).

It still gives the expected result of 1 if there are blank cells in th
range. Ranges where there is more than one unique entry (apart fro
blanks) are also unaffected.

Can anyone give a reason why this would be happening

 

[Montrose77]

I realise I'm responding to my own question, but just had the thought to
try reducing the range, and found that if I reduce it by one column (to
26 cells) then it works as expected.

I guess that is the limit to the array size Excel can handle.

 

[Bob Phillips]

That is not a limitation of arrays, 65535 is, but not 26. Must be something
else. I tried it and added a column in BJ and it worked fine still.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Biff]

I've experienced some strange things using the more conventional formula:

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

Try this in a *new* wb:

Do not enter any data in A1:A10 yet. Enter the above formula in D1. You get
0 which you should. Now, type an entry in A1. You get a #DIV/0! error but
you should have gotten 1. Continue to fill the range A1:A10. You will
continue to get #DIV/0! until you make an entry in A10. Now, clear the range
A1:A10 then try filling it again. This time the formula works as expected.

I've run into something even more strange once, but I can't remember how to
recreate it! I'm thinking this has something to do with the used range not
being set.

Biff

 

[Bob Phillips]

I too have experienced that very same problem, but only occasionally, and as
no-one else had reported it, I assumed it was something on my machine (I do
play with quite a few things on this one). I wonder what it is? Must look
into it now that I know that you also experience it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Montrose77]

Incidentally, the same anomally appears with this formula - try the
following version:

=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

change the number format of the cell to 30 decimal places

and then enter "a" into the cells in column a - you'll notice that at
27 the value changes to "0.999999999999999000000000000000", and then
changes back to "1" at 28, and then alternates between the two and
"0.999999999999998000000000000000" as you work your way towards 100.

This only occurs when there is only one unique entry - once it reaches
"2" the anomally no longer appears.

 

[Bob Phillips]

That must be the precision of the floating point engine.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Biff]

That must be the precision of the floating point engine.

Try this one:

A1:A3 = 1

=SUMPRODUCT((A1:A3<>"")/COUNTIF(A1:A3,A1:A3&""))

The result should be 0.99999999999999~ but gets rounded to 1.

Biff