cross country meet scoring

[tjlutz]

Hi Guys!
I'm trying to workout a scoring spreadsheet for cross country meet. Variables to take into consideration are plenty.
It all hinges on the runners unique competition number. When their number is input, the fields should self-populate that include:
school name, boy or girl, grade (7-9). I got all this figured out, using a vlookup.

Now the fun begins. A school is comprised of at least 5 runners. Runners 6&7 of the same school serve as 'displacers', meaning that if a 6th or 7th runner from a given school, come in before a 1-5 runner from a different school, that different school will earn more points (which a school doesn't want, the lowest score wins).
That's part of the problem, finally, a team who has more than 7 runners do not count in any scoring whatsoever. So beyond a team's 7th runner, they have to 'not score', nor cause another team to increase their points. You see the dilemma. I don't have the skill to iron this one out.
I thank you in advance for your help with this.

#

Name

School

grade

b

g

points

#

Koinonia

grade

points

1​

123​

noah

koinonia

1​

123​

noah

koinonia

b

9​

b

1​

2​

124​

chris

koinonia

2​

124​

chris

koinonia

b

9​

b

2​

3​

132​

rich

hj

3​

125​

jon

koinonia

b

9​

b

12​

4​

133​

al

destiny

4​

126​

dwaine

koinonia

b

9​

b

13​

5​

128​

sam

hj

5​

127​

live

koinonia

b

7​

b

14​

6​

129​

steve

hj

6​

138​

jim

koinonia

b

16​

7​

131​

justin

hj

7​

139​

joe

koinonia

b

19​

8​

134​

bob

destiny

8​

144​

steve

koinonia

b

20​

9​

135​

dan

destiny

9​

HJ cody

b

#N/A​

10​

136​

aaron

destiny

10​

128​

sam

hj

b

8​

b

5​

11​

137​

dennis

destiny

11​

129​

steve

hj

b

8​

b

6​

12​

125​

jon

koinonia

12​

130​

jordan

hj

b

8​

b

15​

13​

126​

dwaine

koinonia

13​

131​

justin

hj

b

8​

b

7​

14​

127​

live

koinonia

14​

132​

rich

hj

b

8​

b

3​

15​

130​

jordan

hj

15​

140​

jack

hj

b

21​

16​

138​

jim

koinonia

16​

141​

ryan

hj

b

22​

17​

145​

dylan

hj

17​

145​

dylan

hj

b

17​

18​

142​

markus

destiny

18​

Destiny

b

#N/A​

19​

139​

joe

koinonia

19​

133​

al

destiny

b

7​

b

4​

20​

144​

steve

koinonia

20​

134​

bob

destiny

b

7​

b

8​

21​

140​

jack

hj

21​

135​

dan

destiny

b

7​

b

9​

22​

141​

ryan

hj

22​

136​

aaron

destiny

b

7​

b

10​

23​

143​

zach

destiny

23​

137​

dennis

destiny

b

7​

b

11​

24​

146​

rylie

destiny

24​

142​

markus

destiny

b

18​

25​

143​

zach

destiny

b

23​

26​

146​

rylie

destiny

b

24​

 

[Joe4]

I am familiar with this, as my son is a Cross Country runner!

I think I would approach this by breaking it apart into "helper" columns. So if we list everyone in the order they finished in, we can do that, like this:

So, column E calculates the place they finish in on their own team.
The formula for cell E2 is:

=COUNTIF($D$2:$D2,$D2)

and copy down for all rows.

Column F calculate they overall finish order, when everything finishing below 7th place on their own team is removed.
The formula for cell F2 is:

=IF(ROW()<=5,ROW()-1,IF(E2<=7,MAX($F$1:$F1)+1,""))

and copy down for all rows.

Column G calculates the number of points the top 5 finishers for each team get.
The formula for cell G2 is:

=IF(E2<=5,F2,"")

and copy down for all rows.

Lastly, if we list all teams in column I, we can get the total points for each team by entering this formula in cell J2:

=SUMIF(D:D,I2,G:G)

and copy down for all rows.

Hope that helps!

 

[tjlutz]

I think this looks great but I have grade issues as well. Again, it comes down to the personal # but grades 7,8 and 9 will run together, each having their own team (of 5, up to 7) so that's another variable needed. Thanks

 

[Joe4]

I think this looks great but I have grade issues as well. Again, it comes down to the personal # but grades 7,8 and 9 will run together, each having their own team (of 5, up to 7) so that's another variable needed. Thanks

I am not clear what you mean.
Are you saying that the 7th graders only compete against other 7th graders, 8th graders against 8th graders, and 9th graders only against 9th graders?
If so, then I would think you would have three different lists (one for each grade), like I showed above.

 

[tjlutz]

I am not clear what you mean.
Are you saying that the 7th graders only compete against other 7th graders, 8th graders against 8th graders, and 9th graders only against 9th graders?
If so, then I would think you would have three different lists (one for each grade), like I showed above.

Hi Joe! You're correct in what I'm wanting to do. 7th vs 7th, 8th vs 8th, etc. But the same way you figured out the place finisher per school, wouldn't the same (similar) formula be used to assign grade levels? The goal is to make the scoring all on one page to keep efficiency high for the task.
I coached CC at the college level and I've never seen this meet before because my school has never competed in it. So I don't know if they run boys and girls separately or together. Thoughts?

 

[Joe4]

Hi Joe! You're correct in what I'm wanting to do. 7th vs 7th, 8th vs 8th, etc. But the same way you figured out the place finisher per school, wouldn't the same (similar) formula be used to assign grade levels? The goal is to make the scoring all on one page to keep efficiency high for the task.
I coached CC at the college level and I've never seen this meet before because my school has never competed in it. So I don't know if they run boys and girls separately or together. Thoughts?

That is going to add a whole other level of complexity, and the formulas will get more complex and/or require additional helper columns. It could get messy!
To be honest, I really do not want to dive into it if we aren't even 100% certain on the requirements (and have it all be for naught).

I have never been to a high school CC meet where the boys and girls run together. But then again, I have never been to a meet where they run different classes to be scored separately together.
That can be really hard on the runners (especially the leaders) if they don't know exactly who they are running against.

 

[tjlutz]

Hey Joe!
I realize the 7,8,9 thing is different. I can't remember if I told you I'm living in Alberta, Canada. I never heard of the scoring method they use until I sat in on the AD meeting. To be honest, I'm completely sure how they score but it's the opposite of the states where they go for most points.
When describing the US scoring, I was getting quizzed about it a little bit. The big thing they were curious about was the scoring beyond the 7th athlete. 6/7 are displacers and don't count to team points. Beyond that, everyone else is running for personal bests, etc. They have everyone scoring whether its 1st or 20th runner, they all score. The debate was about bigger teams always winning vs smaller teams and that's when I chirped in with the US scoring method. So we're going to try it for next year and see how it goes. That's why I'm wanting to build the cross country scorer to make the process as simple as possible for the meet officials. There's no rush for this.
I'm listening to 'Excelisfun', trying to find the formula that will let me accomplish what my ultimate goal is, to input each runner's number and the scoring be complete! I appreciate your help thus far. Breaking it down into helpful columns has been the ticket!

 

[Joe4]

If you are using Excel 365, you may be able to make use of the new FILTER function (I suggest that you update your Account details, or click your user name at the top right of the forum, so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. Don’t forget to scroll down & ‘Save’.)

What I would probably do if I was you, is have everyone on the first sheet, like you have, but then separate them into three different sheets using the FILTER function (one for each grade).
Then, do the temporary columns and calculations on those three tabs, and then feed the points back to the first sheet with a VLOOKUP, XLOOKUP, or SUMIFS formula.