# Thread: Physical test with run time

1. ## Physical test with run time

hello, i have a question regarding this post. I a wrestling coach and marine veteran. I am trying to compute a PT test for my wrestlers. very much like the military, my test consist of push-ups, sit-ups, pull-ups, and a run. i am able to score everything but the run. the wrestlers are scored on a 1.5 mile run at 12 minutes. every 10 seconds over 12 minutes is a point off from 100. I know the marines have a way of doing this on excel, but it's been years since i've done it and i cant find my formula cheat-sheet. also, i can't figure out how to properly insert a run, either mm:ss or [h]m:ss.

secondary question. after each score if calculated, how do i add all four scores up and divide by 400? would it be "=sum(all scores) / 400," or "=(x1+x2+x3+4) / 400."

here's a sample:

female socre: Pull-ups 8=100pts push-ups 35=100pts sit-ups 100=100pts run 1.5 @ 12min = 100pts
results 1: 8 35 87 12mins:06sec
results 2: 5 21 63 15:13

with the pull-ups, sit-ups, and push-ups, i divide the tally with the expected and get the score. I am having a hard time with the run, i can't figure out how to properly insert the time and the formula to calculate the 00:10 seconds over and minus a point from 100. Any help would be highly appreciated

2. Vee,

Welcome to the Lounge as a new poster!

See if this meets your needs?

=100-IF(\$H3<=\$M\$5,0,(INT(((MINUTE(\$H3-\$M\$5)*60)+(SECOND(\$H3-\$M\$5)+5))/10)))

Note 1: The formula is of course based on the setup in the graphic belwo...see attached file.

Note 2: Note the + 5 in the formula near the end. This is a round up value e.g. if 5 seconds it will round up to 10 etc. I didn't know what you want to do with partial units of 10. If you want 11 seconds to count as 20 just change the 5 to a 9, or if you only want to count full 10 second units just delete the +5.

Note 3: The formula is set up to be fully dragable down the column.

vee.JPG

vee.xlsx

HTH

3. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

Vee13 (2014-09-17)

4. RetiredGeek, thank you so much. the samples and information helped out in so many ways. I haven't tried it they formula on the Pull-ups and other yet, but i am assuming with some modification i can make it happen. But you as for the run, it worked magically, thank you.

5. i just applied this to my sheet, and i am having a problem with the expected value. using you sample sheet: M5 is the expected value I5 is the score. when drag the down the expected value drags down as well. M5 is now M6,M7, and so on. i am trying to figure this out but i am having no luck.

6. Vee,

M5 needs to be written as \$M\$4 (an absolute reference) it then will not change as dragged.

Another way to insure Absolute References is to give it a Range Name.
In the Name Box (just above Column A, usually shows the Active Cell) type in a name w/o spaces like ExpectedRunTime and press Enter (you MUST press Enter or it worn't work).
Now where ever you need this value you can just type the name ExpectedRunTime or you can paste it in from the Formulas Tab -> Defined Names section -> Use In Formula dropdown.

The formula becomes: =100-IF(\$H3<=ExpectedRunTime,0,(INT(((MINUTE(\$H3-ExpectedRunTime)*60)+(SECOND(\$H3-ExpectedRunTime)+5))/10)))

This can be done for all the expected values, and IMHO is actually the preferred method.

HTH

7. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

Vee13 (2014-09-22)

8. RetiredGeek, you sir are awesome lol. thank you for simplifying my day.

one more question and i'll be set. on pull-ups for men, the max is 15. i have a few that are doing 15+. how can i score this so that no matter how many they do, over 15, it only scores out of 100points max?

my problem is when i use: "=selected cell'/15 * 100"

example.

i have one with only 10 pull-ups: "=10/15 * 100" = 67 points
another one is 17: "=17/15 * 100" = 113.

how can i set the max. i didn't give enough credit that some will actually pass 15. i learned my lesson.

9. Something like:
=min(100, 'selected cell'/15*100)

Steve

10. ## The Following User Says Thank You to sdckapr For This Useful Post:

Vee13 (2014-09-24)

11. thank you Steve, that did the job.

12. This question was asked and answered earlier. Is there a reason you posted it again?
If you are looking for a modification of RG's formula:
=100-IF(\$H3<=ExpectedRunTime,0,(INT(((MINUTE(\$H3-ExpectedRunTime)*60)+(SECOND(\$H3-ExpectedRunTime)+5))/10)))

You can use the same technique I proposed:
=Min(100,100-IF(\$H3<=ExpectedRunTime,0,(INT(((MINUTE(\$H3-ExpectedRunTime)*60)+(SECOND(\$H3-ExpectedRunTime)+5))/10))))

Steve

13. ## The Following User Says Thank You to sdckapr For This Useful Post:

Vee13 (2014-09-24)

14. Steve, thank you. I honestly do not know why i posted the same question. I guessing it was late on my end and i was just tired lol. But, my goof up actually brought up a curious point for myself. RG's formula begins by "=100-if(\$h3<..."
and your suggested technique begins with "=min(100, 100-if(\$h3<...). i will try both and see the outcome. I thank you and Retiredgeek for the help. thank you both.