frequency for each occurance

[bjg]

HI, I have a vector (as a column in Excel) as follows:

4
4
3
6
3
5
5
3
2
1

Now I want to calculate the frequency of each value (or occurance).
first sort the vector as follows:

1
2
3
3
3
4
4
5
5
6

Then for the small data set I manually calculate the number o
occurance, which is follows:

1 1
2 1
3
3
3 3
4
4 2
5
5 2
6 1

Note the second column shows the number of occurance. I wonder if ther
is any automatical way to do it. Many thanks

 

[Jason Morin]

If you data were in col. A starting in A1 (sorted
ascending), put this in B1 and fill down:

=IF(A1=A2,"",COUNTIF(A:A,A1))

HTH
Jason
Atlanta, GA

 

[bjg]

HI, I applied =IF(A1=A2;COUNTIF(A:A;A1);"") (NOTE: semcolon rather than
comma initally suggested), and it ends up with the following outcome,

1
2
3 3
3 3
3
4 2
4
5 2
5
6

which is not correct. A correct answer should be
1 1
2 1
3 3
3
3
4 2
4
5 2
5
6 1

Any comments are welcome!

 

[N Harkawat]

Assuming your list begins at A2 type the following and copy it down
=IF(AND(COUNTIF(A:A,A2)>1,SUMIF($A$1:A1,A2,$B$1:B1)>1),"",COUNTIF(A:A,A2))