How to set condition for frequency?

[Guest]

Refering to the post in General Question

There is a list of numbers in column A, and list out the possible numbers in
column B, but the list does not contain a fixed number of samples, and the
number does not fixed within any range. On the other words, sometimes, there
are 100 numbers, or 1000 numbers and the number can be any digits in length
I would like to measure the frequency on the listed numbers, if I input
{=Frequency(A:A,B:B)} on column C then #N/A occurs because some cells do not
contain any value for trying to measure frequency.
Does anyone have any suggestions on how to set condition for frequency? if
the cell is blank, then skip measure frequency for this cell only.
Thank for any suggestions
Eric

 

[Domenic]

Is this what you mean?

C1:

=COUNT(FREQUENCY(A:A,B:B))

Then, select your range of cells in Column D, starting with D1, enter
the following formula...

=IF(ROW()-ROW(D$1)+1<=$C$1,FREQUENCY(A:A,B:B),"")

....and confirm with CONTROL+SHIFT+ENTER.

 

[Guest]

[1] Let describe more about the question ---

There is a list of numbers in column A, but the list does not contain a
fixed number of samples, and the number does not fixed within any range. On
the other words, sometimes, there are 100 numbers, or 1000 numbers and the
number can be any digits in length.

[2] What I want to do ---

I would like to measure the frequency on the listed numbers. Firstly, how to
list out all the possible numbers under column B within the list, and then
measure the frequency of occurrence of those numbers under column C, but the
amount of possible numbers is unknown for column B, if I input
{=Frequency(A:A,B:B)} on column C then #N/A occurs because some cells do not
contain any value.

Do you have any suggestions on how to measure frequency?
Thank for any suggestions
Eric

 

[Domenic]

Still unclear. Can you provide a small sample of the data, about 10 or
so rows, along with the expected results?

 

[Guest]

Hi Domenic:
Thank you for your reply

There is a sorted list of numbers under column A, such as
1E-36,0.00056, 0.00056,1,1,1,3,3,99,99,8700000,9100000, which represent a
wide range of numbers, but each number represents a specific item, so I
cannot group any number between 0 and 100 into one group.

Therefore, there are 7 items under the list, and the first task is to list
out each number under column B without duplication, such as following 1E-36,
0.00056, 1, 3, 99, 8700000, 9100000 as a result.

And the next task is to measure the frequency of each number within the list
under column C, in this case, I can simply input {=frequency(A1:A12,B1:B7)}
for cell C1:C7, but one difficulty is that the length of list and the number
of items are not fixed, and I need to handle a dynamic list, therefore, I
cannot define the range A1:A12 and B1:B7 to measure frequency for the cell
C1:C7.

Would it be possible to measure frequency without define the range? such as
{=frequency(A:A,B:B)} for cell C:C. However, if I input {=Frequency(A:A,B:B)}
on column C then #N/A occurs because it seems to me that a blank cell is not
allowed.

Do you have any suggestions?
Would the above statement describes the issue more clear?
I look forward to your reply
Thank you for any suggestions
Eric

Still unclear. Can you provide a small sample of the data, about 10 or
so rows, along with the expected results?

[1] Let describe more about the question ---

There is a list of numbers in column A, but the list does not contain a
fixed number of samples, and the number does not fixed within any range. On
the other words, sometimes, there are 100 numbers, or 1000 numbers and the
number can be any digits in length.

[2] What I want to do ---

I would like to measure the frequency on the listed numbers. Firstly, how to
list out all the possible numbers under column B within the list, and then
measure the frequency of occurrence of those numbers under column C, but the
amount of possible numbers is unknown for column B, if I input
{=Frequency(A:A,B:B)} on column C then #N/A occurs because some cells do not
contain any value.

Do you have any suggestions on how to measure frequency?
Thank for any suggestions
Eric

 

[Domenic]

Let's assume that A2:A13 contains the data, try the following...

Dynamic Named Range:

Insert > Name > Define

Name: DynRange

Refers to:

='Sheet1'!$A$2:INDEX('Sheet1'!$A$2:$A$65536,MATCH(9.99999999999999E+307,'
Sheet1'!$A$2:$A$65536))

Click Ok

Change the sheet reference accordingly.

Formulas:

B2:

=SUM(IF(FREQUENCY(DynRange,DynRange),1))

....confirmed with CONTROL+SHIFT+ENTER.

C2, copied down:

=IF(ROWS($C$2:C2)<=$B$2,INDEX(DynRange,SMALL(IF(DynRange<>"",IF(MATCH(Dyn
Range,DynRange,0)=ROW(DynRange)-MIN(ROW(DynRange))+1,ROW(DynRange)-MIN(RO
W(DynRange))+1)),ROWS($C$2:C2))),"")

....confirmed with CONTROL+SHIFT+ENTER

D2, copied down:

=IF(C2<>"",COUNTIF(DynRange,C2),"")

Hope this helps!

 

[Domenic]

By the way, if you're using Excel 2003 or later version, instead of
defining a dynamic named range, you can convert your data into a list...

Data > List > Create List

The range will automatically adjust as data is added or removed.

Hope this helps!

 

[Guest]

Thank you very much
Eric

By the way, if you're using Excel 2003 or later version, instead of
defining a dynamic named range, you can convert your data into a list...

Data > List > Create List

The range will automatically adjust as data is added or removed.

Hope this helps!