COUNTIF
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 |
COUNTIF
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 |
COUNTIF
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 |
COUNTIF
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 |
All times are GMT +1. The time now is 03:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com