COUNTIF:workable solution

[Simon Minder]

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

[Bearacade]

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

[Toppers]

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

[Simon Minder]

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