Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default How to differentiate groups of numbers


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
--
RodJ
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default How to differentiate groups of numbers

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

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RodJ" wrote in message
...

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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default How to differentiate groups of numbers

Fantastic...this worked beautifully...awesome....thanks..
--
RodJ


"Ashish Mathur" wrote:

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

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RodJ" wrote in message
...

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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default How to differentiate groups of numbers

Thank you for the feedback.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RodJ" wrote in message
...
Fantastic...this worked beautifully...awesome....thanks..
--
RodJ


"Ashish Mathur" wrote:

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

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RodJ" wrote in message
...

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


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default How to differentiate groups of numbers

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




--
RodJ


"Ashish Mathur" wrote:

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

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RodJ" wrote in message
...

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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default How to differentiate groups of numbers

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

"RodJ" wrote in message
...
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 a
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




--
RodJ


"Ashish Mathur" wrote:

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

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RodJ" wrote in message
...

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


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default How to differentiate groups of numbers

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
---
"RodJ" wrote:
Eg: Group: 2,9,10,15,19,20,29,30,37,38,39,40,43

The groups I am seeking a
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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sum only positive numbers for certain groups SteveC Excel Discussion (Misc queries) 4 August 24th 07 03:08 AM
Dragging Groups of Numbers QVCJDN Excel Discussion (Misc queries) 1 May 2nd 06 05:48 PM
Sum groups of numbers in first blank cell Bleu_808 Excel Discussion (Misc queries) 2 May 1st 06 11:23 PM
Groups of numbers evanr Excel Worksheet Functions 1 February 3rd 06 12:18 AM
differences in groups of numbers pm Excel Worksheet Functions 6 August 12th 05 07:09 PM


All times are GMT +1. The time now is 02:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"