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...o&userid=18191
View this thread: http://www.excelforum.com/showthread...hreadid=571345

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.


--
Montrose77
------------------------------------------------------------------------
Montrose77's Profile: http://www.excelforum.com/member.php...o&userid=18191
View this thread: http://www.excelforum.com/showthread...hreadid=571345

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)

"Montrose77" wrote
in message ...

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.


--
Montrose77
------------------------------------------------------------------------
Montrose77's Profile:
http://www.excelforum.com/member.php...o&userid=18191
View this thread: http://www.excelforum.com/showthread...hreadid=571345

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" wrote in message
...
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)

"Montrose77"
wrote
in message ...

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.


--
Montrose77
------------------------------------------------------------------------
Montrose77's Profile:
http://www.excelforum.com/member.php...o&userid=18191
View this thread:
http://www.excelforum.com/showthread...hreadid=571345

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)

"Biff" wrote in message
...
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" wrote in message
...
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)

"Montrose77"
wrote
in message
...

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.


--
Montrose77

------------------------------------------------------------------------
Montrose77's Profile:
http://www.excelforum.com/member.php...o&userid=18191
View this thread:
http://www.excelforum.com/showthread...hreadid=571345

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.

Biff Wrote:
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" wrote in message
...
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)

"Montrose77"

wrote
in message
...

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.


--
Montrose77

------------------------------------------------------------------------
Montrose77's Profile:
http://www.excelforum.com/member.php...o&userid=18191
View this thread:
http://www.excelforum.com/showthread...hreadid=571345





--
Montrose77
------------------------------------------------------------------------
Montrose77's Profile: http://www.excelforum.com/member.php...o&userid=18191
View this thread: http://www.excelforum.com/showthread...hreadid=571345

That must be the precision of the floating point engine.

--
HTH

Bob Phillips

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

"Montrose77" wrote
in message ...

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.

Biff Wrote:
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" wrote in message
...
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)

"Montrose77"

wrote
in message
...

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.


--
Montrose77

------------------------------------------------------------------------
Montrose77's Profile:
http://www.excelforum.com/member.php...o&userid=18191
View this thread:
http://www.excelforum.com/showthread...hreadid=571345





--
Montrose77
------------------------------------------------------------------------
Montrose77's Profile:
http://www.excelforum.com/member.php...o&userid=18191
View this thread: http://www.excelforum.com/showthread...hreadid=571345

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

"Bob Phillips" wrote in message
...
That must be the precision of the floating point engine.

--
HTH

Bob Phillips

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

"Montrose77"
wrote
in message ...

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.

Biff Wrote:
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" wrote in message
...
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)

"Montrose77"

wrote
in message
...

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.


--
Montrose77

------------------------------------------------------------------------
Montrose77's Profile:
http://www.excelforum.com/member.php...o&userid=18191
View this thread:
http://www.excelforum.com/showthread...hreadid=571345





--
Montrose77
------------------------------------------------------------------------
Montrose77's Profile:
http://www.excelforum.com/member.php...o&userid=18191
View this thread:
http://www.excelforum.com/showthread...hreadid=571345