|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
September 25th, 2008, 06:47 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
September 25th, 2008, 07:00 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
September 25th, 2008, 07:09 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
September 25th, 2008, 07:15 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
September 25th, 2008, 08:04 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
September 25th, 2008, 08:18 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
September 25th, 2008, 08:24 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
