Unique entry count
Montrose77
Strange thing has happened with this forumula which was working fine in
my original document, but when I added a new column suddenly started
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 that
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/end
columns updated correctly) it has developed an anomaly which means that
if every cell in the range has the same entry, with no blanks, then the
result is 0.999999999999999000000000000000 (9's to the 15th decimal
point).
It still gives the expected result of 1 if there are blank cells in the
range. Ranges where there is more than one unique entry (apart from
blanks) are also unaffected.
Can anyone give a reason why this would be happening?
--
Montrose77
------------------------------------------------------------------------
Montrose77's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=18191
View this thread: http://www.excelforum.com/showthread.php?threadid=571345
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
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)
"Montrose77" <Montrose77.2cj4v...@excelforum-nospam.com> wrote
in message news:Montrose77.2cj4v...@excelforum-nospam.com...
Biff
=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" <bob...@somewhere.com> wrote in message
news:ue1InQ6v...@TK2MSFTNGP04.phx.gbl...
Bob Phillips
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)
"Biff" <biffi...@comcast.net> wrote in message
news:e6zEyI9v...@TK2MSFTNGP04.phx.gbl...
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
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Montrose77" <Montrose77.2cld7...@excelforum-nospam.com> wrote
in message news:Montrose77.2cld7...@excelforum-nospam.com...
Biff
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
"Bob Phillips" <bob...@somewhere.com> wrote in message
news:ejSgR2Jw...@TK2MSFTNGP06.phx.gbl...