Hi everyone,

I use the following function to count # of unique values in a range:

SUM(N(FREQUENCY(A1:A50,A1:A50)0))

In one file, it works well. In another, I don't get right answer!?
Although I don't know what "N" stands for, I wonder what am I missing
here?

I got this function from www.cpearson.com/excel/duplicate.htm which is
now off-line!

Any help why I am getting wrong answer for the 2nd file?

Regards,
Mike

Try: http://www.cpearson.com/excel/duplicat.htm

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Michael" wrote in message
om...
Hi everyone,

I use the following function to count # of unique values in a range:

SUM(N(FREQUENCY(A1:A50,A1:A50)0))

In one file, it works well. In another, I don't get right answer!?
Although I don't know what "N" stands for, I wonder what am I missing
here?

I got this function from www.cpearson.com/excel/duplicate.htm which is
now off-line!

Any help why I am getting wrong answer for the 2nd file?

Regards,
Mike

note re N() function

it converts to number. often useless as excel does it automatically,
but when using array formulas that produce booleans it's used to force the
boolean to 0/1 so the can be summed/counted and frequented :)


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Rob van Gelder" wrote:

Try: http://www.cpearson.com/excel/duplicat.htm

Do you have an answer to my question please? Why am I getting wrong
counts in one file while okay in another?

Regards,
Mike


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

range probably contains text not numbers.
frequency works with numbers only

cell alignment to general =
if a 'number' remains left aligned you've got your culprit


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Michael Sultan wrote:


Do you have an answer to my question please? Why am I getting wrong
counts in one file while okay in another?

Regards,
Mike


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

keepITcool wrote in message . ..
range probably contains text not numbers.
frequency works with numbers only

cell alignment to general =
if a 'number' remains left aligned you've got your culprit


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Michael Sultan wrote:


Do you have an answer to my question please? Why am I getting wrong
counts in one file while okay in another?

Regards,
Mike


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!


Hi,

I am positive that my range is just numbers, tried also what you said
about alignment but none worked!?

Don't know why the above function works fine in one file but not in
the other one!?

The function again is: SUM(N(FREQUENCY(A1:A10,A1:A10)0))

Mike

It works fine here.

I'm with keepITcool... probably a datatype problem.

Highlight A1:A10 and make sure the numberformat is set to General. That sets
the format but the cell datatype might still be recognised as text.
Retype the numbers in A1:A10 manually. (it's enough just to press F2 on a
cell then press enter)

Does that fix it?


Note: I say manually because for 10 numbers, it's just as easy as the
automated way: copy a cell containing 0, highlight A1:A10, pastespecial with
addition.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Michael" wrote in message
om...
keepITcool wrote in message
. ..
range probably contains text not numbers.
frequency works with numbers only

cell alignment to general =
if a 'number' remains left aligned you've got your culprit


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Michael Sultan wrote:


Do you have an answer to my question please? Why am I getting wrong
counts in one file while okay in another?

Regards,
Mike


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!


Hi,

I am positive that my range is just numbers, tried also what you said
about alignment but none worked!?

Don't know why the above function works fine in one file but not in
the other one!?

The function again is: SUM(N(FREQUENCY(A1:A10,A1:A10)0))

Mike

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

"Rob van Gelder" wrote in message ...
It works fine here.

I'm with keepITcool... probably a datatype problem.

Highlight A1:A10 and make sure the numberformat is set to General. That sets
the format but the cell datatype might still be recognised as text.
Retype the numbers in A1:A10 manually. (it's enough just to press F2 on a
cell then press enter)

Does that fix it?


Note: I say manually because for 10 numbers, it's just as easy as the
automated way: copy a cell containing 0, highlight A1:A10, pastespecial with
addition.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Michael" wrote in message
om...
keepITcool wrote in message
. ..
range probably contains text not numbers.
frequency works with numbers only

cell alignment to general =
if a 'number' remains left aligned you've got your culprit


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Michael Sultan wrote:


Do you have an answer to my question please? Why am I getting wrong
counts in one file while okay in another?

Regards,
Mike


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!


Hi,

I am positive that my range is just numbers, tried also what you said
about alignment but none worked!?

Don't know why the above function works fine in one file but not in
the other one!?

The function again is: SUM(N(FREQUENCY(A1:A10,A1:A10)0))

Mike


Hi,

I know the problem now! However, the way you described how to fix is
not working!?

BTW, the values that I am using come from a solver. One value was,
say, .1234567
and the another was .12345665! How can I limit the values size in the
VBA code so they can be compared properly?

Do you refer to this as font.size?

Regards,
Mike

You've told me you know what's wrong. Is it what keepITcool and I said it
was?

Tell us what else you've tried to fix the problem?

You could compare two rounded versions:
=ROUND(A1, 5)


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Michael" wrote in message
om...
"Rob van Gelder" wrote in message
...
It works fine here.

I'm with keepITcool... probably a datatype problem.

Highlight A1:A10 and make sure the numberformat is set to General. That
sets
the format but the cell datatype might still be recognised as text.
Retype the numbers in A1:A10 manually. (it's enough just to press F2 on
a
cell then press enter)

Does that fix it?


Note: I say manually because for 10 numbers, it's just as easy as the
automated way: copy a cell containing 0, highlight A1:A10, pastespecial
with
addition.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Michael" wrote in message
om...
keepITcool wrote in message
. ..
range probably contains text not numbers.
frequency works with numbers only

cell alignment to general =
if a 'number' remains left aligned you've got your culprit


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Michael Sultan wrote:


Do you have an answer to my question please? Why am I getting
wrong
counts in one file while okay in another?

Regards,
Mike


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!


Hi,

I am positive that my range is just numbers, tried also what you said
about alignment but none worked!?

Don't know why the above function works fine in one file but not in
the other one!?

The function again is: SUM(N(FREQUENCY(A1:A10,A1:A10)0))

Mike


Hi,

I know the problem now! However, the way you described how to fix is
not working!?

BTW, the values that I am using come from a solver. One value was,
say, .1234567
and the another was .12345665! How can I limit the values size in the
VBA code so they can be compared properly?

Do you refer to this as font.size?

Regards,
Mike

"Rob van Gelder" wrote in message ...
You've told me you know what's wrong. Is it what keepITcool and I said it
was?

Tell us what else you've tried to fix the problem?

You could compare two rounded versions:
=ROUND(A1, 5)


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Michael" wrote in message
om...
"Rob van Gelder" wrote in message
...
It works fine here.

I'm with keepITcool... probably a datatype problem.

Highlight A1:A10 and make sure the numberformat is set to General. That
sets
the format but the cell datatype might still be recognised as text.
Retype the numbers in A1:A10 manually. (it's enough just to press F2 on
a
cell then press enter)

Does that fix it?


Note: I say manually because for 10 numbers, it's just as easy as the
automated way: copy a cell containing 0, highlight A1:A10, pastespecial
with
addition.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Michael" wrote in message
om...
keepITcool wrote in message
. ..
range probably contains text not numbers.
frequency works with numbers only

cell alignment to general =
if a 'number' remains left aligned you've got your culprit


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Michael Sultan wrote:


Do you have an answer to my question please? Why am I getting
wrong
counts in one file while okay in another?

Regards,
Mike


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!


Hi,

I am positive that my range is just numbers, tried also what you said
about alignment but none worked!?

Don't know why the above function works fine in one file but not in
the other one!?

The function again is: SUM(N(FREQUENCY(A1:A10,A1:A10)0))

Mike


Hi,

I know the problem now! However, the way you described how to fix is
not working!?

BTW, the values that I am using come from a solver. One value was,
say, .1234567
and the another was .12345665! How can I limit the values size in the
VBA code so they can be compared properly?

Do you refer to this as font.size?

Regards,
Mike


Hi,

Try this and see for yourself please:

..323
..323
..323
..323
..323
..323
..323
..466
..638
..638
..638
..954
..954
..954
5 (enter a function sum(N(frequency(a1:a14,a1:a14)0)) )

The correct answer should be 4 NOT 5!?!

Mike

I get 4 on my computer.

Try performing this same test on a new workbook/worksheet.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Michael" wrote in message
om...
"Rob van Gelder" wrote in message
...
You've told me you know what's wrong. Is it what keepITcool and I said
it
was?

Tell us what else you've tried to fix the problem?

You could compare two rounded versions:
=ROUND(A1, 5)


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Michael" wrote in message
om...
"Rob van Gelder" wrote in
message
...
It works fine here.

I'm with keepITcool... probably a datatype problem.

Highlight A1:A10 and make sure the numberformat is set to General.
That
sets
the format but the cell datatype might still be recognised as text.
Retype the numbers in A1:A10 manually. (it's enough just to press F2
on
a
cell then press enter)

Does that fix it?


Note: I say manually because for 10 numbers, it's just as easy as
the
automated way: copy a cell containing 0, highlight A1:A10,
pastespecial
with
addition.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Michael" wrote in message
om...
keepITcool wrote in message
. ..
range probably contains text not numbers.
frequency works with numbers only

cell alignment to general =
if a 'number' remains left aligned you've got your culprit


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Michael Sultan wrote:


Do you have an answer to my question please? Why am I getting
wrong
counts in one file while okay in another?

Regards,
Mike


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!


Hi,

I am positive that my range is just numbers, tried also what you
said
about alignment but none worked!?

Don't know why the above function works fine in one file but not
in
the other one!?

The function again is: SUM(N(FREQUENCY(A1:A10,A1:A10)0))

Mike


Hi,

I know the problem now! However, the way you described how to fix is
not working!?

BTW, the values that I am using come from a solver. One value was,
say, .1234567
and the another was .12345665! How can I limit the values size in the
VBA code so they can be compared properly?

Do you refer to this as font.size?

Regards,
Mike


Hi,

Try this and see for yourself please:

.323
.323
.323
.323
.323
.323
.323
.466
.638
.638
.638
.954
.954
.954
5 (enter a function sum(N(frequency(a1:a14,a1:a14)0)) )

The correct answer should be 4 NOT 5!?!

Mike