Formula for counting age groups

Hello all.
I'm a bit of a novice when it come to formulas and have been trying to
figure this out all day so I would really appreciate help sorting this
out.
I have a spreadsheet which contains ages from 18 to 85+ but I need it
to tell me how many of each group (18-64, 65-74, 75-84, 85+).
Please can anyone assist.

Thanks

Col
0
9/26/2008 4:57:40 PM
excel 39879 articles. 2 followers. Follow

3 Replies
337 Views

Similar Articles

[PageSpeed] 43

You can use

=COUNTIF(A2:A2000,">=18")-COUNTIF(A2:A2000,">64")

then


=COUNTIF(A2:A2000,">=65")-COUNTIF(A2:A2000,">74")


and


=COUNTIF(A2:A2000,">=75")-COUNTIF(A2:A2000,">84")


and finally


=COUNTIF(A2:A2000,">=85")



Another way might be using FREQUENCY


-- 


Regards,


Peo Sjoblom

"techcs" <colinsealeaf@blueyonder.co.uk> wrote in message 
news:28098258-50ba-4a8c-923e-7259ee58f8aa@25g2000hsx.googlegroups.com...
> Hello all.
> I'm a bit of a novice when it come to formulas and have been trying to
> figure this out all day so I would really appreciate help sorting this
> out.
> I have a spreadsheet which contains ages from 18 to 85+ but I need it
> to tell me how many of each group (18-64, 65-74, 75-84, 85+).
> Please can anyone assist.
>
> Thanks
>
> Col 


0
peo.sjoblom (169)
9/26/2008 5:14:26 PM
On 26 Sep, 18:14, "Peo Sjoblom" <peo.sjob...@mvps.org> wrote:
> You can use
>
> =COUNTIF(A2:A2000,">=18")-COUNTIF(A2:A2000,">64")
>
> then
>
> =COUNTIF(A2:A2000,">=65")-COUNTIF(A2:A2000,">74")
>
> and
>
> =COUNTIF(A2:A2000,">=75")-COUNTIF(A2:A2000,">84")
>
> and finally
>
> =COUNTIF(A2:A2000,">=85")
>
> Another way might be using FREQUENCY
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "techcs" <colinseal...@blueyonder.co.uk> wrote in message
>
> news:28098258-50ba-4a8c-923e-7259ee58f8aa@25g2000hsx.googlegroups.com...
>
>
>
> > Hello all.
> > I'm a bit of a novice when it come to formulas and have been trying to
> > figure this out all day so I would really appreciate help sorting this
> > out.
> > I have a spreadsheet which contains ages from 18 to 85+ but I need it
> > to tell me how many of each group (18-64, 65-74, 75-84, 85+).
> > Please can anyone assist.
>
> > Thanks
>
> > Col- Hide quoted text -
>
> - Show quoted text -

Speedy response.
Many many thanks, saved me a lot of work.

Col
0
9/26/2008 5:38:06 PM
Hi,

Suppose your data is in the range D1:D100

1.  In three cells, say A1:A3, enter 
64
74
84
2.  Highlight the range B1:B4 (yes B4)
3. Type, but do not enter, the formula 

=FREQUENCY(D1:D100,A1:A3)

4. Press Shift+Ctrl+Enter

-- 
Thanks,
Shane Devenshire


"techcs" wrote:

> Hello all.
> I'm a bit of a novice when it come to formulas and have been trying to
> figure this out all day so I would really appreciate help sorting this
> out.
> I have a spreadsheet which contains ages from 18 to 85+ but I need it
> to tell me how many of each group (18-64, 65-74, 75-84, 85+).
> Please can anyone assist.
> 
> Thanks
> 
> Col
> 
0
9/27/2008 6:57:01 AM
Reply:

Similar Artilces:

Is it OK to assign permissions to a group during ForestPrep of an E2K3 upgrade?
Hello, We're about to start an upgrade from E2K to E2K3. During the E2K3 ForestPrep, we want to select a newly-created group to assign Exchange full admin permissions to instead of an individual user; however, during our previous E2K ForestPrep a few years ago, we only selected an individual user. Could there be any conflicts in now giving Exchange admin permissions to a new group instead of the same individual user as before? Sorry if it appears to be a silly question but I'm paranoid. Thanks, - Alan. On 27 Oct 2006 05:35:52 -0700, "Alan" <bruguy@gmail.com> wro...

Excel 2003 "IF" formula (I think!!)
hello Fellow Excel users, I wanted to use a formula to calculate one rate at certain percentageof return and another rate to calculate if greater than that return. For example: assuming that you have a return on an investment of 25% during a given year. I am trying to automatically calculate a split between two different parties at different level of return such as having one party get the first 10% of the return then an 80/20 split for the portion between 10%-13% and 50/50 split between the portion between 13%-20% and back to 80/20 split between the portion greater than 20% return. Can this b...

Hiding Exchange 2003 user's group membership in Outlook 2003
When a user X sends an email to user Y, user Y can find out all groups user X belongs to (both Security & Distribution Groups and those Groups' members) by simply double clicking on the sender's name in Outlook 2003. This could be serious a security issue in some deployments. Is there a way to hide the groups a user belongs to? I can hide the members of Distribution Groups (user X is a member of) from user Y, by configuring "Hide membership" of those Groups using Exchange Tasks option with AD-Users/Groups tool. However, I cannot hide the members of Security Groups (...

Counting results of a formula
I have a column that has about 111 rows to it. In each row is a formula that will find an invoice number if it exists elsewhere. The are alpha-numeric invoice #s. I need to count the number of invoices found. If I use the COUNTA formula for that column it will count those cells that have the fomula in it but no result. I only want to count those cells that have a result from the formula. Any ideas for using Excel 2003? Thanks. =COUNTIF(G1:G111,">""") Gord Dibben MS Excel MVP On Tue, 4 May 2010 10:10:01 -0700, Nadine <Nadine@discussions.mi...

formula to return 1st of the month following 60 days
Hello - I need, please, a formula to return the following. So, if someone started working with us on 10/15/09, the 1st of the month following 60 days of employment would be 1/1/10. That's what I need. THANK YOU! What about 1st of a month say 10/1/2009 'If you expect 1/1/10 then use =DATE(YEAR(A1),MONTH(A1)+3,0)+1 'if you expect 12/1/09 then use the below version =DATE(YEAR(A1-1),MONTH(A1-1)+3,0)+1 Regards Jacob "Jessica" wrote: > Hello - > > I need, please, a formula to return the following. So, if someone started > ...

Counting the number of fields in a column that have data?
I have an Excel document with about 5,300 rows. One of the columns has data entered only into some of the fields, maybe 100 of the 5,300 rows. Is there an easy way to get Excel to count all of the fields in that column that have data entered in it? Thanks! On 1/25/10 4:57 PM, in article eW3gilgnKHA.1548@TK2MSFTNGP02.phx.gbl, "Rick" <someone@yahoo.com> wrote: > I have an Excel document with about 5,300 rows. One of the columns has > data entered only into some of the fields, maybe 100 of the 5,300 rows. > > Is there an easy way to get Excel to cou...

formula to show 97.826 as 97.825
I have a sheet where i need to show all numbers ending in 0 or 5 so 98.321 would show as 98.320 and 98.324 would show as 98.325 etc... I am obviously not gr8 at excel so would appreciate som assistance Cheers Kevin First, make sure the Analysis ToolPak is installed! Menu: Tools>Add-Ins...> tick the Analysis ToolPak if it is not alread ticked. Then use this formula: =MROUND(A1,5) It will round to the nearest 5-multiple. HTH Ola Sandstr� -- olas ----------------------------------------------------------------------- olasa's Profile: http://www.excelforum.com/member.php?actio...

Hide "Grouped" Controls
Hi Folks - I have 15 controls (labels, text boxes and lines) that I would like to hide for a certain set of criteria. I know I can set each control's visible property to false, but I'm wondering if there is a way to 'group' the controls together, then just hide the group. Any ideas? Thanks. Michael You can use the TAG property and give each of the controls you want to show/hide the same tag. Then you can loop through the controls, check the tag value and show or hide that way. 'UNTESTED AIRCODE (with no error handling) Private Sub sShowStuff(Optional tfShow as ...

SUMMARIZING DATA BASED ON DATES GROUPED IN WEEKS
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C6ED3C.00C7C2B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello! 1) The RAW DATA SHEET is where I maintain a cash receipt register in a = list form on a daily basis. Any date may have multiple entries due to = different customer payment. 2) The SUMMARY SHEET shows how I want to summarize the data contained in = RAW DATA SHEET. Basically the summary sheet is summing based on the date = grouped in weeks. =20 RAW DATA SHEET DATE DAY CUSTOMER AMOUNT=20 ...

Please help w/formula
I want to create a formula for an employee schedule to compute the totals hrs scheduled, multiply total hrs scheduled by rate of pay to get a gross rate of pay. I can only get it to add up to 24 hrs then it starts over at 1. Example I am using: Start:17:00 End: 19:00 Total: =(end-start) =IF(A3<A2,A3+1-A2,A3-A2)*24*B2 Where A2 is start, A3 is finished, B2 is rate of pay Format cell to currency or accounting. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Dave" wrote: > I want to create a formula for an employee schedule to co...

saving data from a formula into a variable
hi all, does anyone know how to save data coming form formulaarray directly to a variable? How about you getting your formula to work in the worksheet? Then copy|paste that working formula into your reply. It'll be easier to modify that than to start from scratch. yaniv.dg@gmail.com wrote: > > hi all, > does anyone know how to save data coming form formulaarray directly to > a variable? -- Dave Peterson haven't we been here before? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) <yaniv.dg@gmail.com> wrote in mess...

Grouping Calculations
I’ve created a report in which invoices are grouped by vendor and then by service period. I’ve calculated the total invoice amount. But I also need to calculate the average invoice amount per service period. The problem I’m having is that there aren’t the same number of service period by month groupings per vendor. Please help. Jen wrote: >I�ve created a report in which invoices are grouped by vendor and then by >service period. I�ve calculated the total invoice amount. But I also need >to calculate the average invoice amount per service period. The problem I�m >hav...

Formulas Referencing Pivot Table results
I'm using a pivot table to sum batting statistics. I calculate the batting average by referencing cells in the table. However, when I add a new batter or remove a batter, and refresh the table, the formula references are incorrect. Why is that and is there a way to maintain formula references as new batters are added/removed? Thanks John It sounds like you could use a calculated field in your pivottable. show the pivottable toolbar if it's not already shown. click on PivotTable dropdown click on formulas click on calculated field Give it a nice name (Batting Average?). an...

Importing from Windows Mail: lost my contact groups
Is there anyway to get my contact groups back? I imported from Windows Mail in Vista to Outlook 07. All my addresses are fine, but I don't have my contact groups. No, contact groups are not exportable. You'll need to make the groups in Outlook (where they are called distribution lists). -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mai...

HOW TO FORMULA NUMBER TO WORDS
Give tips to transfer the number to words its very use full to us Have a look here http://www.ozgrid.com/VBA/ValueToWords.htm Mike "ramaraj" wrote: > Give tips to transfer the number to words its very use full to us http://www.xldynamic.com/source/xld.xlFAQ0004.html -- __________________________________ HTH Bob "ramaraj" <ramaraj@discussions.microsoft.com> wrote in message news:2D61ED9B-5144-4743-A0C3-3BE19BAF5F9C@microsoft.com... > Give tips to transfer the number to words its very use full to us Hi, 1. Copy the data...

Count values once no matter how often they appear
I'm trying to sum data in Sheet1 from 50+ other sheets in the same file, using =SUM(EUN1!AD8+EUN2!AD8+SV!AD8....and so on. However, I need to ammend the formula every time new sheet is added. Is there a formula which will automatically count the values in the respective cell in all sheets, irrespectively how many are added? Thanks a million! Try the below..Sheet1 is the 1st sheet and Sheet3 is the last sheet...Try inserting new sheets inbetween...Keep the first and last sheets names constant...and always make sure to insert in between =SUM(Sheet1:Sheet3!AD8) -- Ja...

complex formula
I need help creating a formula. I have 4 columns and 12 rows of data. When the number in the cell reaches multiples of 3 it creates 1 occurance. So 6 in a cell would create 2 occurances. However 4 in one column and 2 in the second column would only create 1 occurance. I tried countif >2 but that says that 6 is only 1 occurance. Any suggestions? Hi there. You may want to try: =int(sum(A1:D12)/3) Regards, Otavio "Debi" wrote: > I need help creating a formula. I have 4 columns and 12 rows of data. When > the number in the cell reaches multiples of ...

Group By 10-20-07
How would I Group By all except for one possible entry. Within the query “qryCall”, I need to group by all possibilities except for “Canceled On-scene”. Thanks -- Message posted via http://www.accessmonster.com "pushrodengine via AccessMonster.com" <u36225@uwe> wrote in news:79fa7663ed3f0@uwe: > How would I Group By all except for one possible entry. > > Within the query “qryCall”, I need to group by all > possibilities except for “Canceled On-scene”. > > Thanks > Create a calculated field CanceledOnScene: [fieldname] = "Canceled On-scene...

How to count matching text
Let's say you have a list of hundreds of confirmation numbers, they can be text, numeric or alpa-numeric, how can I know how many of the entries are exact duplicates vs how many are different. Example: HVT88947 HVT88947 HVT88948 HVT88948 9784268 712589 DDKLM DDKLM HVT88946 712589 Ok, we have 10 entries above. Four of them are duplicate entries, six of them are different entries, right? Ok, so lets say you now have thousands of these entries and you know there are lots of duplicates somewhere in the list. How can you calculate how many are duplicate entries and how many are differ...

script for aged trial balance and historical aged trial?
There was a post several months ago about a script / process to recreate the aged trial balance and historical aged trial balance reports in Crystal or some other reporting tool. Did anyone receive the material from Shariq on the process he had developed for this? And will you share it? Thanks. -- Jim@TurboChef ------=_NextPart_0001_29012E03 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi and thanks for posting your question on newsgroups. As I'm sure you are aware we don't have any tools like this internally. I will leave it up to the community to see if they c...

Auditing formulas month over month
I tried a few searched, but had no luck in finding a solution. We have a few very complex spreadsheets, and am looking to find a way to ensure completeness and accuracy of the spreadsheets month over month. Currently we do the following: 1) Upon creation of spreadsheet, audited all formulas to make sure that are calculating as expected. 2) Month over month we select a sample of row to ensure that the formulas match the baselined formulas. This is done manually. What I would like to do is change part 2 so that we just create a worksheet that automatically checks the formulas against the bas...

Self joining table and grouping help
Hi All, Could some help me with the SQL without using PARTITION and RANKING functions? Or would that be asking too much? pk_id, parent_id, trx_date, amt ====================================== 1, 0, 3/7/2008, 2500 3, 0, 5/2/2008, 5000 48, 0, 12/4/2009, 2000 109, 0, 12/9/2009, 5000 101, 48, 12/6/2009, 2000 118, 48, 12/13/2009, 4000 145, 48, 12/13/2009, 2000 229, 48, 12/20/2009, 2000 313, 48, 12/27/2009, 2000 397, 48, 1/3/2010, 2000 482, 48, 1/10/2010, 48400 122, 109, 12/13/2009, 1365 149, 109, 12/13/2009, 5000 233, 109, 12/20/2009, 5000 317, 109, 12/27/2009, 5000 401, 109...

How do i count the number of text Entries of one word over a range
To explain here is an example of the columns i have: THC will not Span Nox will not Span THC fails AVU THC will not Span I would like to be able to count how many times the phrase "THC" is mentioned over the course of hundreds of data entry columns? With this example i would look to have the answer of 3. Please can anyone help. Thanks =COUNTIF(A:A,"*THC*") -- Regards! Stefi „Chris Eaton” ezt írta: > To explain here is an example of the columns i have: > > THC will not Span > Nox will not Span > THC fails AVU...

Distribution group #6
all members does not recive mail with have been send to a distribution group. Why? I have a distrubution group with three members and when sending mail to it. sometimes only 1 or 2 recipient are getting the message. From exchange view i can see that the mail as been delivered to the users mailstore but he can�t find it. Hilfe Fredrik Edstrom If a message has been delivered to a user's mailbox, that's where you'll find it. - check for any rules that may have moved it to Junk Mail or any other folder, or deleted the message. -- Bharat Suneja MCSE, MCT www.zenprise.com b...

Count unique values for rows meeting specific criteria?
Been going around in circles with this for a while.. part way there, but not completely.. I need to COUNT the number of UNIQUE values in Column A, but only count rows if columns Q and R meet a certain criteria. I have used this formula for the total unique values: =(SUM(IF(FREQUENCY($A$2:$A$5507,$A$2:$A$5507)>0,1))) Now I would like to know if I can use this formula, or if I need to use something else to get my answer. The two criteria I need to be met before the row can be included in the unique value calculation are: ($Q$2:$Q$5507>=DATE(1998,6,31)) AND ($R$2:$R$5507<=DATE(1...