Top 12 Scores based on specific criteria

I am a teacher and I really need help! I am trying to create a formula that will give the top 12 results but make sure that at least 4 is the opposite gender. How can I do this? I am attaching my file. Any help will be appreciated!

Thanks,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Formula Problem isn't any more descriptive of your issue - as stated above, Use terms appropriate to a Google search - what would you search for if you were searching for your problem? Once you've done this you're more likely to get responses - right now some will ignore the thread because the title is so generic.

When you say 'highest averages' it seems that you mean 'highest scores' - the numbers in the second columns of each table.

I can see how you get 2988 for the first table (Hamilton) - that's the sum of the 12 highest scores, of which 6 are M and 6 are F, so meet your other criterion.

I think you've just made a mistake with the second table (Tupelo) and 2727. The top 12 scores sum to 2957, with 5 x M and 7 x F, but you seem to have taken just the top 11 scores - was that just a mistake or am I missing something?

I can't see at all how you get 3019 for the third table (Starkville). The top 12 scores sum to 3058 and judging by the names seem to be 4 x F and 8 x M. How did you get 3019?

Right now, I can't see an easy solution to this. It will need to involve finding the top 4 male scores, top 4 female scores and then ranking the remaining scores to get the other 4 you want. Making sure the same name/score isn't double-counted is going to be complicated though. I suspect it will involve adding a few helper tables (or at least helper columns to existing tables) to do intermediate calculations.
Before any of us take this any further, can you please confirm how you get to the numbers you have?

Also, please confirm that the names in the tables are made up? If not, there is an issue with confidentiality, so please remove the attachment you have and replace it in which the names are not real - just call them 'Child 1' 'Child 2' etc - the only essential info is the scores and the genders.
If you need to remove the attachment but can't, click 'Report Post' on your post and ask a moderator to remove it for you.

Okay, I've got a solution for you, using six (!) helper columns - though you can hide five of them once they're done.

1. First, you need to Sort the Scores column - Smallest to Largest or vice-versa doesn't matter. If you don't do this, then if there's a tie-break you could get 13 scores instead of 12 (column H deals with the tie-break by assigning different ranks, so 2 x 10= becomes 10 and 11).

2. Now insert the extra columns to the right of the table and put the following headings in row 3:

Formula:

Please Login or Register  to view this content.

3. Enter the formulae below into the relevant cells on row 4 and copy down the columns:

Formula:

Please Login or Register  to view this content.

4. Select Sum at the bottom of column I (cell I28) - you should get 2988 as you expected.

5. Now you can hide columns D to H if you want.

6. Repeat for the other tables.


It's a bit messy, but I think this does what you asked for. There's probably a 'better' way to do it, with fewer helper columns, but I can't work that out right now.

I've attached a file showing this working - it's based on your previous attachment, but with 'Archer 101' etc in place of the names.

Hope that does what you want.

Just insert columns to the right of the other tables then copy D3:I4 to the new columns and drag the formulae down. As long as your table headings are the same in each table (Score and Gender are the important ones) then the formulae should work fine for any table you copy them to.

Actually, if you have lots of tables to do, it'll probably be easier to copy the table with the extra columns, paste it elsewhere, erase the data in the 'Archer' 'Score' and 'Gender' columns (leave the formulae, obviously). Then you can just copy that as many times as you need, then copy/paste the data from previous tables.

Just make sure there's at least one empty column between each table.

The top-scoring Male in each table is the one with the lowest number in the M-rank column, the top scoring Female is the one with the number 1 in the F-rank column.

Assuming you want to put it below your table, try this:

In the cell below where it says 'Total', type 'Top Female'.
In the cell to the right of that, put in this formula:

Formula:

Please Login or Register  to view this content.

In the cell below where you typed 'Top Female', type 'Top Male'.
In the cell to the right of that, put in this formula:

Formula:

Please Login or Register  to view this content.

You'll need to change the table reference for each table, from the Table3 in the formulae above to whatever the name of each table is.
To find out the name of each table, click somewhere in it, then click the Table Tools tab which appears on the ribbon. The name is at the left-hand end.

This may be of help.
I followed Aardigspook's instructions about putting the top female and male below the line with the total, then I added cells to retrieve their scores using:

Formula:

Please Login or Register  to view this content.

I then linked Tupelo's top scorers and scores (Starkville's table couldn't be completed because there were no gender designations) below Hamiltons.
I used the following array entered formula* to identify the overall top female and male:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.