Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif to pur a persons age into selected caragories.
Hi
I have been task to find a formula to be able to count peoples ages into select different age groups for a quartely report. The age groups i have to catagories them into is 18-21 , 22- 31, 32-41, 42-51, 52 -61 year olds The example is in column A, but my spreadsheet contains over 126 for each month. Age 16 24 45 50 23 60 Please can you help with a formula to help catorgries the aboe ages into the allocated groups. Your help would be most appreciated. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200706/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif to pur a persons age into selected caragories.
Without using array formulas:
=COUNT(A:A)-COUNTIF(A:A,"<18")-COUNTIF(A:A,"21") Etc. __________________________________________________ ____________________ "chedd via OfficeKB.com" <u18187@uwe wrote in message news:73c2604b4198a@uwe... Hi I have been task to find a formula to be able to count peoples ages into select different age groups for a quartely report. The age groups i have to catagories them into is 18-21 , 22- 31, 32-41, 42-51, 52 -61 year olds The example is in column A, but my spreadsheet contains over 126 for each month. Age 16 24 45 50 23 60 Please can you help with a formula to help catorgries the aboe ages into the allocated groups. Your help would be most appreciated. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200706/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif to pur a persons age into selected caragories.
Try something like this:
For 18 through 21 =SUM(COUNTIF(A:A,{"<18","<=21"})*{-1,1}) For 22 through 31 =SUM(COUNTIF(A:A,{"<22","<=31"})*{-1,1}) For 32 through 41 =SUM(COUNTIF(A:A,{"<32","<=41"})*{-1,1}) etc. Does that help? *********** Regards, Ron XL2002, WinXP "chedd via OfficeKB.com" wrote: Hi I have been task to find a formula to be able to count peoples ages into select different age groups for a quartely report. The age groups i have to catagories them into is 18-21 , 22- 31, 32-41, 42-51, 52 -61 year olds The example is in column A, but my spreadsheet contains over 126 for each month. Age 16 24 45 50 23 60 Please can you help with a formula to help catorgries the aboe ages into the allocated groups. Your help would be most appreciated. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200706/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif to pur a persons age into selected caragories.
=SUMPRODUCT(--(A1:A100=18),--(A1:A100<=21))
or =COUNTIF(A:A,"=18")-COUNTIF(A:A,"=21") "chedd via OfficeKB.com" wrote: Hi I have been task to find a formula to be able to count peoples ages into select different age groups for a quartely report. The age groups i have to catagories them into is 18-21 , 22- 31, 32-41, 42-51, 52 -61 year olds The example is in column A, but my spreadsheet contains over 126 for each month. Age 16 24 45 50 23 60 Please can you help with a formula to help catorgries the aboe ages into the allocated groups. Your help would be most appreciated. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200706/1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif to pur a persons age into selected caragories.
Hi,
Here are four other approaches: 1. The shortest approach - use the FREQUENCY function: suppose your Ages are in column A, starting in cell A2 and suppose you enter the number 21,31,41,51,61 in cells E5:E9. Then select the range F5:F9 and type the formula =FREQUENCY(A2:A26,E5:E9) but don't press Enter, instead press Shift Ctrl Enter. 2. Use a pivot table: Put your cursor in the Age column and create the pivot table. Put Age in both the Row and Data areas! Change the Data calculation to Count. In the pivot table put your cursor in the row area and choose PivotTable, Group and Show Detail, Group and set the Start at to 12, the End at to 61 and the By to 10. There will be a lable in the row area 12-21, type over this with 18-21. 3. Using the same range as #1 enter the following formula in F5: =COUNTIF(A$2:A$26,"<="&E5)-SUM(F$4:F4) This formula assumes F4 is empty. 4. You can use the Data Analysis Add-in's Histogram tool. -- Cheers, Shane Devenshire "chedd via OfficeKB.com" wrote: Hi I have been task to find a formula to be able to count peoples ages into select different age groups for a quartely report. The age groups i have to catagories them into is 18-21 , 22- 31, 32-41, 42-51, 52 -61 year olds The example is in column A, but my spreadsheet contains over 126 for each month. Age 16 24 45 50 23 60 Please can you help with a formula to help catorgries the aboe ages into the allocated groups. Your help would be most appreciated. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200706/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif to pur a persons age into selected caragories.
Fantastic and thank you for sparing the time to help.
Ron Coderre wrote: Try something like this: For 18 through 21 =SUM(COUNTIF(A:A,{"<18","<=21"})*{-1,1}) For 22 through 31 =SUM(COUNTIF(A:A,{"<22","<=31"})*{-1,1}) For 32 through 41 =SUM(COUNTIF(A:A,{"<32","<=41"})*{-1,1}) etc. Does that help? *********** Regards, Ron XL2002, WinXP Hi [quoted text clipped - 17 lines] Your help would be most appreciated. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200706/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I am trying to lookup a persons name in a table. | Excel Worksheet Functions | |||
caculate a persons age | Excel Worksheet Functions | |||
what formula do i need to calculate a persons age | Excel Discussion (Misc queries) | |||
Excel for Blind persons use | Excel Discussion (Misc queries) | |||
How can I create a field indicating a persons age by using bd inf | Excel Worksheet Functions |