Formula for Sport Rankings

[titoesco]

Individual and team (sport) rankings:
• 14 individual rankings and 1 team ranking (15 total worksheets)
• Need excel formula for team rankings. All the individual rankings for a team to make the sum of their tournament points.
• Here is my tournament team ranking formula: If ranked 1=24 team points, if ranked 2=20 team points, if ranked 3=16 team points, if ranked 4=14 team points, if ranked 5=11 team points, if ranked 6=9 team points, if ranked 7=7 team points, if ranked 8=6 team points, if ranked 9 thru 12= 4 team points.
• Ex. "School A" has 3 individuals ranked 1, 2 & 5. "School A" tournament points are 24 (rank 1) + 20 (rank 2) + 11 (rank 5)= 55

This sounds harder than it really is. Maybe a visual would work better?

Thank you! -Tito

[leo73pk]

Its better you provide some data file enabling readers to work out any solution for your problem

[titoesco]

I have attached the excel file for help.

Thanks!

[leo73pk]

I think in the attached sheet, you should clearly define the headings, to establish a point table/ranking of teams.

what does sheet numbers reflect?,
what is meant by Column G values In individual sheets.?

What are the inputs and how you want the appearance of point table?

[titoesco]

Ok, here is the new file that I've simplified. For the Team Tournament Points I have done the first one already to show you what the correct number should be. The individual tournament points on Class A-C do not need to be added on the sheet if they are included into the formula.

Ranking Individual Tournament Points
1 24
2 20
3 16
4 14
5 11
6 9
7 7
8 6
9 4
10 4
11 4
12 4

Thank you!

[MarvinP]

Is this what you were looking for?

See attached.

[DonkeyOte]

I agree with MarvinP that separate SUMIFs are the way forward (else use a Multi Consolidation Pivot Table)

You can conduct SUMIF in 3D if you use INDIRECT etc but it's volatile and not particularly efficient IMO

Please Login or Register  to view this content.

where _Sheets is a Dynamic Named Range defined as:

Please Login or Register  to view this content.

and where sheets to be included in the aggregation (Class A, Class B & Class C) are listed in Column G (G1 onwards)

but to reiterate I concur with MarvinP's approach - the above is merely a demo. of an alternative technique you might otherwise come across.

(Given use of XL2008 UDFs are not viable)

[titoesco]

Marvin,
That looks close thank you. I think what I exactly want is not possible. The amount of schools are over 1,000 and random. I just put down a few for example. Would it be possible in the "Team Rankings" if I were to type a school name it would populate the scores from the all the individual classes? So if I were to type the school "Foothill" is would go through all the classes and add up the total tournament points from only the "Foothill" school. I'm sorry if I'm not explaining it well. I'm just looking for an easier way of go through all the classes and adding up the individual points that make up the school's total tournament points and ranking them.

Thank you so much for the help!

[MarvinP]

Hi titoesco,

I was wondering if you had all the same school names. I understand the classes as I was a 175 many years ago. Also made it to state.

The answer is all dependent on how you have your data arranged and stored. Pivot Tables could be great as it doesn't need the school or league name to be pre-defined. You could also filter by weight class and/or league or division.

One worksheet per class is not optimal. If you had a single sheet with the weight class as a separate column it would be much better.

Is your original attachment how the data is stored? I think with some easy moving of data around to make a single table a Pivot Table is the answer.

[titoesco]

No, I don't have all the school names and new schools will be entered with the new rankings bi-weekly. I don't know what a pivot table is. Here is how the data is show (except for the team rankings I would like to have the tournament points for each of the ranked teams). Check attached.

[DonkeyOte]

FWIW, attached is a file I put together earlier when answering your thread - perhaps somewhat overlooked (sob)

It demo's both the SUMPRODUCT/INDIRECT approach and also (perhaps more importantly) the Multi Consolidation Pivot.

Regards not knowing what a Pivot Table is - if in doubt research - there's a general intro. link in my sig and here is an overview of a Multi Consolidation Pivot.
(see also: http://www.contextures.com/xlVideos10.html#Multi as referenced on above link)

[MarvinP]

Hi
Find the attached where I've put all the weight sheets onto a single sheet called All. I have also created an example Pivot Table on this sheet with Weight and Section filters above it. Drop them down and click on them to filter the Pivot. What I think you need it Ranking Points for all the individual records of data. I don't know how you gave points, so I used the ranking numbers instead (which may be meaningless).

See if this helps you understand how the data could be structured and anlyised using Pivot Tables.

Sorry for the delay but football company showed up and I needed to be a host.

[titoesco]

Wow! Thank you! This is coming along real nice. Here are the individual tournament points according to the rankings that I am giving:

1. 24
2. 20
3. 16
4. 14
5. 11
6. 9
7. 7
8. 6
9. 4
10. 4
11. 4
12. 4

I'm not sure where to substitute those.

[MarvinP]

Hi,
This was the part I didn't understand. If 103, Perez was ranked 1 does he get 24 points? I didn't know how assign points based on rankings.

I'll assume that is the case and give points and put that in the Pivot Table and attach it. I just need a little more understanding about points vs rankings.

I've added a second pivot table based on the All data to allow filtering by league/section. You can click the dropdown next to Row Labels in the Pivot table to sort the data from large to small to see who won.

BTW - where is my old school, Pioneer High School in San Jose Unified? Are they part of this? See the Attached with the new Points column and pivot tables.

I hope this helps. If not let me know what else you need.

[titoesco]

Yeah, those points are correct! I have one small change. Can I leave the 11th and 12th rankings for each weight on the sheet but take off their tournament points that go towards the Team total points? Right now I am just ranking the top 10 but down the road I might to change it to top 12. Also, can you add another column in the team pivot table next to the school and total for the number of individuals "ranked" for each school? I think you may have had it on the last one. Thank you!

[MarvinP]

To make the 11th and 12th ranks equal to no points look at the formula in column I. I just make the 11th and 12th ranks 0 instead of 4. No problem for that one. I've added a count of ranks onto the first pivot. If you want the count to only include ranks above a certain number you will need to click on the Row Label drop down and specify to only include ranks less than 11.

Find the attached with your changes. Good wrestling!

[titoesco]

Marvin, This is perfect! Thank you! Thank you so much!