I'm currently doing a college course and I've hit a bit of a stumbling block. A question says to create a spread sheet for 20 new applicants to a gym. This I've done as you can see. One question asks to calculate the percentage of male to female ratio, this will go into box next to Eiii. Is there a formula to work this out? The second question asks to work out the subscriptions for the year for junior (11 - 18), full (19 - 64) and senior (65 - 80) members. Again I've no idea how to write something for this that will be able to determine the groups, sort them and multiply by the correct subscription amount. Is there anyone cleverer than me (without a doubt) that has any idea on how this is / can be done please? Pretty please.
Ha. Easy if you know how. But I dont. I use excel a lot but mainly for processing calculations. I would be interested to know the solution.
I'll take a look at this tonight when I get home, the first thing you need is COUNTIF() function for example =COUNTIF(B2:B50,1) =COUNTIF(B2:B50,2) that will count the number of one's and two's in the second column from which you can work out the percentage male female, it could also work if the column contained M and F and the conditions was modified to "M" and "F" for example =COUNTIF(B2:B50,"M") There are a number of other ways to use the COUNTIF() function to partition your list into age groups for example =COUNTIF(A2:A50,">=11") - COUNTIF(A2:A50,"<=18") would count all those aged 11 to 18 and likewise you can partition the other ranges as well.
I don't use spreadsheets much these days, but I did use them a lot back in the late 1980's, they are great modelling tools, great for 'what if' types of questions but too many people end up using them as databases and get themselves into a mess, accountants tend to do that a lot. But the range of options available in a spreadsheet these days is truly huge and with enough will you can force them to behave like a dedicated computer program but that is usually taking things to far
A little bit of jiggling around but it works spot on now. =COUNTIF(A4:A23,">11") - COUNTIF(A4:A23,">18") For the subscription part =(COUNTIF(A4:A23,">11")-COUNTIF(A4:A23,">18"))*360 Thanks for the assistance, a massive help
Good stuff. If it had been me I would have put the ones and twos in seperate columns and summed them. It would have worked but would have been far less tidier than with Oss's solution.
Yeah but what he meant was take Stu's ones and two's and put the markers in two separate columns and sum them, not the literal values, that's the way I read it. What he meant was an =IF(B9=1,1,0) in one column =IF(B9=2,1,0) in the next column and a sum at the bottom of each of these columns.
Or... You could sum the 1s in each column. Then work out the ratio in a seperate cell for the male to female ratio. Nowhere near as neat. But it works.
People here use Mathcad for their engineering stuff, Oss. I use Excel for any maths that needs to be done. I do sometimes wish I knew more of Excels features though. This one that Stu has illustrated is a good one.
