+ Reply to Thread
Results 1 to 18 of 18

Percentage of people in an age group?

  1. #1
    Registered User
    Join Date
    05-09-2006
    Location
    Leamington Spa, UK
    Posts
    24

    Thumbs up Percentage of people in an age group?

    I've been trying for a while to find this formula, maybe it's not possible with Excel 2000. I've tried here before but still can't find the solution having searched again now. I need this formula twice over to get statistical data for my clients. The formula should work for both sections with a minor alteration for each.

    I need to find the percentage of people that are in each age range, i.e. 21-30, 31-40, 41-50 years old and so on from looking at the whole column. In the column, D5:D30 in another spreadsheet, the individuals exact age is given in the cell.

    Is it possible and does anyone know the answer? Thank you for your help.
    Last edited by ianonline; 03-20-2007 at 07:50 AM.

  2. #2
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208
    It's hard to tell the exact formula based on the fact given, but try something like this:

    =SUMPRODUCT((D5:D30>=21)*(D5:D30<=30))/COUNTA(D5:D30)

    HTH

    Jason

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    This will count number of ages between 21 and 30

    =Countif('Sheet2'!$D$5:$D$30,">=21")-Countif('Sheet2'!$D$5:$D$30,">30")

    if you have 21 and 30 in two separate cells, say A1 and B1, then you can try:

    =Countif('Sheet2'!$D$5:$D$30,">=" & A1)-Countif('Sheet2'!$D$5:$D$30,">" & B1)

    change Sheet2 to match the name of the sheet the data is in.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Registered User
    Join Date
    05-09-2006
    Location
    Leamington Spa, UK
    Posts
    24

    Thumbs up This is proving very difficult!

    Ok well I've made progress, the age formula works fine, thank you, after I counted them and got a percentage from the total which is hidden (summed in a cell the same colour as the text). For this section, it is in column N and this works:
    =COUNTIF('LPL Players Comprehensive'!$N$5:$N$30,">=18")-COUNTIF('LPL Players Comprehensive'!$N$5:$N$30,">25")

    But the other section is not as easy as I thought, but I suspect you know the answer. I need to find the percentage of players who played over X percentage of games in a season. For example, how many over 90%, 80% and so on based on data in the other spreadsheet.

    There's a column (column R4 downwards) with exact figures of how many games each person has played in, but I can't seem to get it to count them this time. When I get a formula that works, I get a percentage in the thousands!

    Thanks for your help.

    BTW, it's an event management company and the client is a local bar and this will show that most players aren't students and that regular players spend a lot of money (hopefully!).
    Last edited by ianonline; 03-20-2007 at 09:05 AM.

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    This would be for ages between 18 and 21 and over 80% played.

    =SUMPRODUCT(--('LPL Players Comprehensive'!$N$5:$N$30>=18)-COUNTIF('LPL Players Comprehensive'!$N$5:$N$30>25),--('LPL Players Comprehensive'!$R$5:$R$30/$X$1>80%))

    where X1 houses number of games total played in the season....

  6. #6
    Registered User
    Join Date
    05-09-2006
    Location
    Leamington Spa, UK
    Posts
    24

    Thumbs up

    The ages don't matter here, it's the percentage of those playing over X%.

    I tried the formula you suggested, and without changing anything I get an error that says there are not enough arguments.

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Sorry, i forgot to take out the Countif part in that formula...

    should be: =SUMPRODUCT(--('LPL Players Comprehensive'!$N$5:$N$30>=18),--('LPL Players Comprehensive'!$N$5:$N$30>25),--('LPL Players Comprehensive'!$R$5:$R$30/$X$1>80%))


    If you want to just count the percentages part, then:

    =SUMPRODUCT(--('LPL Players Comprehensive'!$R$5:$R$30/$X$1>80%))

    again, where X1 in both formulas would be the total number of games played.

  8. #8
    Registered User
    Join Date
    05-09-2006
    Location
    Leamington Spa, UK
    Posts
    24

    Unhappy

    This seems impossible!

    So I entered this (but the corrected source of the data) as you said:
    =SUMPRODUCT(--('[LPL Leaderboard.xls]Bar44 - LPL - Season 1'!$R$5:$R$30/$X$1>80%))

    I put the number of games in X1 but all I get is #DIV/0

    Any ideas?!

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    It should work...see attached...

    I tried it referencing anothere workbook too and it was fine.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    05-09-2006
    Location
    Leamington Spa, UK
    Posts
    24

    Thumbs up

    I'm not totally sure how it works, but I checked on a calculator and it does work in the file you sent, which is what matters. Now why won't it work in my spreadsheet?! I tried it all within the same worksheet asking how many players played 40% to 50% of the games because a few have and the result comes up as zero!

    I've attached your file with mine included in the spreadsheet, can you take a look at it? I've tried uisng the number of games in R2 and T2 but I still get zero. The result should be in P10

    Six games out of ten played and two of 26 players have played in all games, so it should show 8% of people have played all 6 of the ten games so far. It doesn't! Can you see why?
    Last edited by ianonline; 03-20-2007 at 09:02 PM.

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    You are causing a circular reference error. You have two formulas referencing each other....the formulas in Column R and conflicting with the formulas In P since formulas in R include results in P and results in P depend on results in R...

    If you place the formula outside the range, say in T10, it should work.....

    If you have to place the formula in P, then can you use the numbers in F?

  12. #12
    Registered User
    Join Date
    05-09-2006
    Location
    Leamington Spa, UK
    Posts
    24

    Unhappy

    For a moment, I actually thought it was working...

    To make it easier, I've moved the source data into the same spreadsheet and the total number of games into the same (Stats) worksheet.

    F43 - total number of games.
    C5-C500 (on other sheet) - times players played.

    So...

    =SUMPRODUCT(--('LPL Players Comprehensive'!$C$5:$C$500/F43>50%))

    It now seems to work except it counts everyone who's played more than 50% of the games, not just 50-59% which makes it look like half the players have only played 1 game in the season.
    Last edited by ianonline; 03-20-2007 at 01:44 PM.

  13. #13
    Registered User
    Join Date
    05-09-2006
    Location
    Leamington Spa, UK
    Posts
    24

    Unhappy

    For a moment, I actually thought it was working...

    To make it easier, I've moved the source data into the same spreadsheet and the total number of games into the same (Stats) worksheet.

    F43 - total number of games.
    C5-C500 (on other sheet) - times players played.

    So...

    =SUMPRODUCT(--('LPL Players Comprehensive'!$C$5:$C$500/F43>50%))

    It now seems to work except I think it includes everything above 50%, not just 50-59%, and the current formula tells me that of 26 registered players, 473 have never played!

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    You'll have to add another criteria...

    =SUMPRODUCT(--('LPL Players Comprehensive'!$C$5:$C$500/F43>50%),--('LPL Players Comprehensive'!$C$5:$C$500/F43<=59%))

  15. #15
    Registered User
    Join Date
    05-09-2006
    Location
    Leamington Spa, UK
    Posts
    24
    This could drive a person insane trying to get it right!

    =SUMPRODUCT(--('LPL Players Comprehensive'!$C$5:$C$500/F43>10%),--('LPL Players Comprehensive'!$C$5:$C$500/F43<=19%))

    That's the formula you suggested and I did and the result is that nobody has ever played! I've checked every part, ever referenced cell, it looks fine. There's no errors coming up, just a zero trying to look innocent!
    Last edited by ianonline; 03-20-2007 at 09:02 PM.

  16. #16
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Your criteria were not actually including the round percentages such as 10%, 20%, 30%, 40%, etc... and since your number of games happened to be 10 (all the results would be a number divisable by 10).

    To include those percentages update formulas...

    in B47: =SUMPRODUCT(--('LPL Players Comprehensive'!$C$5:$C$500/F43>=90%))

    in B48: =SUMPRODUCT(--('LPL Players Comprehensive'!$C$5:$C$500/F43>=80%),--('LPL Players Comprehensive'!$C$5:$C$500/F43<=89%))

    etc...

  17. #17
    Registered User
    Join Date
    05-09-2006
    Location
    Leamington Spa, UK
    Posts
    24

    Talking It Works!

    I'm not sure what you did different from me as I did try very similar formulas, but it works! It also seems to work if I change the number of games. Thank you NBVC for your help.



    The bad news is the majority have only played one game, but this is not an accurate reflection as it is most players first season.

    The site is not live at this time of writing, but hopefully the effect of this statisical data will show in the game results on www.leamingtonpoker.co.uk.

    It also gives me a good idea, give away a small poker thing for free on players 5th or so game. I just hope that duplicating and using the sheets again will not prove too difficult, I doubt it now.

    I am thinking I may do one of the courses on the adverts on the left of this message, I could do with it! Thanks again for your help NBVC and let me know if I can do anything for you. For now, I'll just give this thread an "excellent" rating.

    Last edited by ianonline; 03-20-2007 at 09:05 PM.

  18. #18
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Wink

    Hi ianonline,

    I'm very happy it worked out for you..

    and thanks for your positive feedback. I am glad to help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1