Excel Frequency Function?

[Michael]

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

 

[Rob van Gelder]

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

 

[keepITcool]

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 >

 

[Michael Sultan]

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]

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]

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 >

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

 

[Rob van Gelder]

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.

 

[Michael Sultan]

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

 

[Michael]

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.

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]

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)

 

[Michael]

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)

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

 

[Rob van Gelder]

I get 4 on my computer.

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