Home |
Search |
Today's Posts |
#1
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
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
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
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
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
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
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum only positive numbers for certain groups | Excel Discussion (Misc queries) | |||
Dragging Groups of Numbers | Excel Discussion (Misc queries) | |||
Sum groups of numbers in first blank cell | Excel Discussion (Misc queries) | |||
Groups of numbers | Excel Worksheet Functions | |||
differences in groups of numbers | Excel Worksheet Functions |