How to differentiate groups of numbers

[RodJ]

eg: number group = 2,12,18,35,36,40. I need a function that will tell me how
many numbers are under 22 and how many are over 23.
Answer I am looking for is:
Under 22 = 3
Over 22 = 3.

Is there a function that can give me this answer. Thanks

 

[Ashish Mathur]

=countif(range,"<22") and =countif(range,">22")

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[RodJ]

Fantastic...this worked beautifully...awesome....thanks..

 

[Ashish Mathur]

Thank you for the feedback.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

Fantastic...this worked beautifully...awesome....thanks..

 

[RodJ]

Just wondered whether u can help me solve this:
Again I want to group a set of numbers as follows"

Eg: Group: 2,9,10,15,19,20,29,30,37,38,39,40,43

The groups I am seeking are:
Numbers within 1-9 = answer above is 2
Numbers between 10 and 19 = answer is 3
Numbers 20 and 29 = answer is 2
Numbers between 30 and 39 = answer is 4
Numbers between 40 and 45 = answer is 2

Would like to know how to use countif function or any other function to
obtain the above answers.

Thanks
RodJ

 

[Ashish Mathur]

Hi,

Have the upper and lower limits in range A1:B5. In cell C1, enter the
following formula

=SUMPRODUCT((range>=A1)*(range<=B1))

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

Just wondered whether u can help me solve this:
Again I want to group a set of numbers as follows"

Eg: Group: 2,9,10,15,19,20,29,30,37,38,39,40,43

The groups I am seeking are:
Numbers within 1-9 = answer above is 2
Numbers between 10 and 19 = answer is 3
Numbers 20 and 29 = answer is 2
Numbers between 30 and 39 = answer is 4
Numbers between 40 and 45 = answer is 2

Would like to know how to use countif function or any other function to
obtain the above answers.

Thanks
RodJ

 

[Max]

Try FREQUENCY, its quite suitable for this

Assume the source numbers are in A2:A14 (data_array)
In B2:B6, list the upper limits: 9,19,29,39,45 (bins_array)

Then select C2:C7**, paste this into the formula bar:
=FREQUENCY(A2:A14,B2:B6)
and press CTRL+SHIFT+ENTER to confirm the formula
(this is a multi-cell array formula)
**select a range 1 cell more than the bins_array

C2:C7 will return the desired results
C7 returns the count of any values above the highest interval (>45)
(you can test C7's return by changing one or 2 of the source values in A2:A14)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100 Files:360 Subscribers:56
xdemechanik