frequency for each occurance
 

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). I
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 of
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 there
is any automatical way to do it. Many thanks.


--
bjg
------------------------------------------------------------------------
bjg's Profile: http://www.excelforum.com/member.php...o&userid=16776
View this thread: http://www.excelforum.com/showthread...hreadid=319730

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

-----Original Message-----

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). I
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 of
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 there
is any automatical way to do it. Many thanks.


--
bjg
---------------------------------------------------------
---------------
bjg's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=16776
View this thread:
http://www.excelforum.com/showthread...hreadid=319730

.

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!


--
bjg
------------------------------------------------------------------------
bjg's Profile: http://www.excelforum.com/member.php...o&userid=16776
View this thread: http://www.excelforum.com/showthread...hreadid=319730

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))

"bjg" wrote in message
...

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!


--
bjg
------------------------------------------------------------------------
bjg's Profile:
http://www.excelforum.com/member.php...o&userid=16776
View this thread: http://www.excelforum.com/showthread...hreadid=319730