"Counting" data by number of occurrences.

[mikewvdos]

I have a list of data (about 2000 data points), and I need to "count" how many times each singular data point occurs within a range. For example, lets say I only have 4 data points as follows:

A=2%
B=6%
C=2%
D=4%

In this instance, I want excel to tell me that there are 2 occurences of a value between 0% and 2%, one occurence between 2.1% and 4%, and one occurence between 4.1% and 6%.

Clear as mud ? Thanks for your help. Yes, I know this is probably a very simple excel question, but I guess thats why I'm here - because I need help with the very simple.

mike in Calgary

 

[Aladin Akyurek]

I have a list of data (about 2000 data points), and I need to "count" how many times each singular data point occurs within a range. For example, lets say I only have 4 data points as follows:

A=2%
B=6%
C=2%
D=4%

In this instance, I want excel to tell me that there are 2 occurences of a value between 0% and 2%, one occurence between 2.1% and 4%, and one occurence between 4.1% and 6%.

Clear as mud ? Thanks for your help. Yes, I know this is probably a very simple excel question, but I guess thats why I'm here - because I need help with the very simple.

mike in Calgary

E1: 0%
F1: 2.1%
G1: 4.1%

E2, copied across to G2:

=COUNTIF($A$1:$D$1,">="&E1)-COUNTIF($A$1:$D$1,">"&IF(F1="",6%,F1))

 

[Gates Is Antichrist]

Here's a semi-long way to do it, though someone may have a tighter solution.

foo.xls
	A	B	C	D	E	F	G
1						count	cumulative
2							0
3	2%		0%	to	2%	2	2
4	6%		2.1%	to	4%	1	3
5	2%		4.1%	to	6%	1	4
6	4%		6.1%	to	8%	0	4
7			8.1%	to	10%	0	4
Sheet3

 

[PaddyD]

or use a pivot table:

select the range, put the data in the row & count of data in the data area. then, right click the row, select group & outline, enter you min & max for the range & the interval & you're done - quicker to do than explain.