Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Posts
    6
    Thanks
    4
    Thanked 0 Times in 0 Posts

    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. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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

    Also, thank you for your service!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    Vee13 (2014-09-17)

  4. #3
    New Lounger
    Join Date
    Sep 2014
    Posts
    6
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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. #4
    New Lounger
    Join Date
    Sep 2014
    Posts
    6
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.
    Place your cursor on the Expected Value in your workbook.
    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
    Last edited by RetiredGeek; 2014-09-17 at 17:57.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    Vee13 (2014-09-22)

  8. #6
    New Lounger
    Join Date
    Sep 2014
    Posts
    6
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #8
    New Lounger
    Join Date
    Sep 2014
    Posts
    6
    Thanks
    4
    Thanked 0 Times in 0 Posts
    thank you Steve, that did the job.

  12. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #10
    New Lounger
    Join Date
    Sep 2014
    Posts
    6
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •