Athletic Spread Sheet Formulas

If you have updated to 365, then you can use these formulae.


For place (lowest time):

Formula:

Please Login or Register  to view this content.

For place (longest distance):

Formula:

Please Login or Register  to view this content.

For points:

Formula:

Please Login or Register  to view this content.

Just to neaten up my points formula:

Formula:

Please Login or Register  to view this content.

That is very neat. And for position, a slight edit:

Formula:

Please Login or Register  to view this content.

How would you adjust it for the distance events? Long jump, etc?

Ah, simple. Should have remembered. Thanks.

You're welcome. Thanks for the rep.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

If the event record is beaten, row 2 for each event, then the athletes is highlighted.
but if we could also colour code the current record with the highlight colour to make it easy to see??

You would use Conditional Formatting. For example, select $B$7:$D$31 and use the formula

Formula:

Please Login or Register  to view this content.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar or even the same as this thread, we have a rule that you open your own thread on the issue and do not piggy back another member's thread.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

I added the CF rule to B7 and applied it to $B$7:$D$31. You'd need to adapt it yourself for other ranges. Is this what you wanted?

Now that you have seen my suggestion, if that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

It would be much better for you to learn how to do it yourself.

I will give you the steps for the 100m race, then you need to follow them for the others.

1. Select cell A7 (the first name on the list).
2. On the Home ribbon, click Conditional Formatting > Manage Rules.
3. Click on New Rule > Use a formula ...
4. Type the rule into the box: =AND($B7<>"",$B7<$B$2)
5. Choose your formatting > OK.
6. In the Applies to box, type this: =$A$7:$A$31,$B$7:$D$31
7. Click OK.

I have done this in the workbook attached. Now, you follow these instructions to set up the rule for the 200m race:

1. Select cell A7 (the first name on the list).
2. On the Home ribbon, click Conditional Formatting > Manage Rules.
3. Click on New Rule > Use a formula ...
4. Type the rule into the box: =AND($E7<>"",$E7<$E$2)
5. Choose your formatting > OK.
6. In the Applies to box, type this: =$A$7:$A$31,$E$7:$G$31
7. Click OK.

I've highlighted the changes you need to make each time you set up a rule. ALWAYS have A7 selected when setting rules. For Coochin you'd select A36 and so on.

For high jump:

=AND($E7<>"",$K7>$K$2,$K7=MAX($K$7:$K$31))

Try again (just noticed the 'm').

High jump: =AND($E7<>"",$K7>--SUBSTITUTE($K$2,"m",""),$K7=MAX($K$7:$K$31))

200m: =AND($E7<>"",$E7<$E$2,$E7=MIN($E$7:$E$31))

100m: =AND($B7<>"",$B7<$B$2,$B7=MIN($B$7:$B$31))

You are making progress.

It's like I need to reverse the rules for sprints.......

Well, yes, of course you do!!!

Track events - lowest time wins.
Field events - highest distance/height wins.

There are NO rules in the workbook you have attached, though, so I can't troubleshoot what you are doing wrong.

So you have completely ditched the CF rules I gave you and demostrated to you in the attachment to post #26 - why? Are they no longer needed?

This is now a question about something else entirely - these are formulae, not CF rules. I am utterly confused!

But, to answer your question about the formulae to rank players, you need ASCENDING for track events:

=IFERROR(RANK(B7,B$7:B$89,1),"")

and DESCENDING for field events:

=IFERROR(RANK(K7,K$7:K$31,0),"")

OK.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

My formulae in post #26 do what you want. You haven't used them, which is why it's not working.

Look at the attachment I provided in post #26. I am not going to do it all again - sorry.

In what way is it NOT workning? The new record can't highlight until it is broken.

For example, the 100m rule:

=AND($B7<>"",$B7<$B$2,$B7=MIN($B$7:$B$89))

B7 has to:

1. NOT be empty.
2. Be LESS than the current record.
3. Be the lowest value in the range.

So it cannot highlight if any of those three rules are untrue.

Oh, I see!!! Gotcha - give me a mo.

OK - rule for B2:

=COUNTIF($B$7:$B$89,"<"&$B$2)>0

Applies to: $B$2

Example for field events:

=COUNTIF($B$7:$B$89,">"&$B$2)>0

Use this rule for field events:

=SUMPRODUCT(($K$7:$K$89>--SUBSTITUTE($K$2,"m",""))*(ISNUMBER($K$7:$K$89)))>0

The problems are:

1. The "m" in the value in K2 which turns it into text (I covered this in the other rule earlier in the thread).
2. Text in the column is counted as bigger than 0, so you need to exclude it.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

It does work - I tested it. I'll see what you've done.

Read point 2 of my earlier post.

If you get rid of the 'm' in the Records sheet, use this rule:

=SUMPRODUCT(($K$7:$K$89>$K$2)*(ISNUMBER($K$7:$K$89)))>0

Then change the other rule to this:

=AND($K7<>"",$K7>$K$2,$K7=MAX($K$7:$K$89))

i.e.remove the SUBSTITUTE clause and change E7 at the start to K7.

I think you have more than enough now to be able to complete the remainder of the field events, but you do need to check your rules very carefully and make sure that they are referencing the correct cells in each case.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.