Hi all,

I have a huge data range with values between -5 and +4. I would like to
count the values with the following groups:
-5 to -4
-4 to -3
-3 to -2
-2 to -1
-1 to 0
0 to 1
1 to 2
2 to 3
3 to 4

e.g. The value 0.5 would go into the group "0 to 1". The value 1 would still
go into the group "0 to 1" and not into the group "1 to 2".

I tried to find a workable solution with the COUNTIF function, but I had no
luck.

Does anybody know a solution for this problem?

Kind regards,

Simon Minder

Try:

=COUNTIF($A$1:$A$50,"" & B1)-COUNTIF($A$1:$A$50,""& C1)

where B1=0, C1=1 to give results for 0 to 1 (0.0001 to 1.0000)

HTH

"Simon Minder" wrote:

Hi all,

I have a huge data range with values between -5 and +4. I would like to
count the values with the following groups:
-5 to -4
-4 to -3
-3 to -2
-2 to -1
-1 to 0
0 to 1
1 to 2
2 to 3
3 to 4

e.g. The value 0.5 would go into the group "0 to 1". The value 1 would still
go into the group "0 to 1" and not into the group "1 to 2".

I tried to find a workable solution with the COUNTIF function, but I had no
luck.

Does anybody know a solution for this problem?

Kind regards,

Simon Minder

Assuming the A1:A100 is your data.

B1 = '-5 to -4
B2 = '-4 to -3
B3 = '-3 to -2
B4 = '-2 to -1
B5 = '-1 to 0
B6 = '0 to 1
B7 = '1 to 2
B8 = '2 to 3
B9 = '3 to 4

C1 = =COUNTIF(A1:A100,"-5")-COUNTIF(A1:A100,"-4")
C2 = =COUNTIF(A1:A100,"-4")-COUNTIF(A1:A100,"-3")
C3 = =COUNTIF(A1:A100,"-3")-COUNTIF(A1:A100,"-2")
C4 = =COUNTIF(A1:A100,"-2")-COUNTIF(A1:A100,"-1")
C5 = =COUNTIF(A1:A100,"-1")-COUNTIF(A1:A100,"0")
C6 = =COUNTIF(A1:A100,"0")-COUNTIF(A1:A100,"1")
C7 = =COUNTIF(A1:A100,"1")-COUNTIF(A1:A100,"2")
C8 = =COUNTIF(A1:A100,"2")-COUNTIF(A1:A100,"3")
C9 = =COUNTIF(A1:A100,"3")-COUNTIF(A1:A100,"4")

Hope that helps


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=551085

Hi Bearacade,

Thank you very much for your support.

I only had to replace the comma by a ';' and everything worked. See below:

=COUNTIF(A1:A100;"-5")-COUNTIF(A1:A100;"-4")
=COUNTIF(A1:A100;"-4")-COUNTIF(A1:A100;"-3")
=COUNTIF(A1:A100;"-3")-COUNTIF(A1:A100;"-2")
=COUNTIF(A1:A100;"-2")-COUNTIF(A1:A100;"-1")
=COUNTIF(A1:A100;"-1")-COUNTIF(A1:A100;"0")
=COUNTIF(A1:A100;"0")-COUNTIF(A1:A100;"1")
=COUNTIF(A1:A100;"1")-COUNTIF(A1:A100;"2")
=COUNTIF(A1:A100;"2")-COUNTIF(A1:A100;"3")
=COUNTIF(A1:A100;"3")-COUNTIF(A1:A100;"4")

Kind regards,

Simon Minder

"Bearacade" wrote:


Assuming the A1:A100 is your data.

B1 = '-5 to -4
B2 = '-4 to -3
B3 = '-3 to -2
B4 = '-2 to -1
B5 = '-1 to 0
B6 = '0 to 1
B7 = '1 to 2
B8 = '2 to 3
B9 = '3 to 4

C1 = =COUNTIF(A1:A100,"-5")-COUNTIF(A1:A100,"-4")
C2 = =COUNTIF(A1:A100,"-4")-COUNTIF(A1:A100,"-3")
C3 = =COUNTIF(A1:A100,"-3")-COUNTIF(A1:A100,"-2")
C4 = =COUNTIF(A1:A100,"-2")-COUNTIF(A1:A100,"-1")
C5 = =COUNTIF(A1:A100,"-1")-COUNTIF(A1:A100,"0")
C6 = =COUNTIF(A1:A100,"0")-COUNTIF(A1:A100,"1")
C7 = =COUNTIF(A1:A100,"1")-COUNTIF(A1:A100,"2")
C8 = =COUNTIF(A1:A100,"2")-COUNTIF(A1:A100,"3")
C9 = =COUNTIF(A1:A100,"3")-COUNTIF(A1:A100,"4")

Hope that helps


--
Bearacade


------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=551085