Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Marine Corps PFT Calculator and Tally Sheet (MS Office 2000)

    <P ID="edit" class=small>(Edited by HansV on 15-May-03 19:09. Attachment removed temporarily because it may contain confidential information)</P>Before I get deep into my problem the first thing I would like to know is there anyone that is in the Marine Corps or is out of the Marine Corps that know what I am talking about when I talk about formulas for adding up the PFT Scores by Age group/Sex/and the different types of scoring between Male and Female. Or if someone can look at the spreadsheet and see if they can figure out what I am doing?
    What I know is that I will be using a lot of IF commands in the formulas but instead of asking each one I was hoping that maybe someone out there has already done one of these up and could share it with me, or maybe help me out with this one.
    I am attaching my spreadsheet and if you look over all the TABs then you may get a feel of what I am trying to do.

    Most Appreciated
    Eric

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 340 Times in 333 Posts

    Re: Marine Corps PFT Calculator and Tally Sheet (MS Office 2000)

    Here is a link to some software that claims to be an "easy PFT calculator"
    http://www.greenhousecomputing.com/pages/e...I2RqqorUeD7c4H5

    It is NOT clear to me, what formulas you need help with calculating, but I was never a marine and never had to do one of these. Are you just trying to get summary tables of the data by different criteria? A pivot table based on your "tally" sheet might be the answer, if I understand the question.

    Steve

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Marine Corps PFT Calculator and Tally Sheet (MS Office 2000)

    Yes I have access to a PFT Calculator already what I am trying to do is on the spreadsheet format, a Tally sheet were I enter the persons name, ssn, age, and so on. Within that I am wanting to have the points tallied up for each position i.e., (pullups for male,flex arm hang time for females, crunches for both that was easy, and run time) the pullups and crunches was to easy what is got me confused is the difference for the female and the male scoring system which I believe the IF command will come in handy and the run time which is also different between Male and Female. Once all said and done I will be putting into age groups and total scores to come up with what class PFTer this Marine is.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 340 Times in 333 Posts

    Re: Marine Corps PFT Calculator and Tally Sheet (MS Office 2000)

    If I understand what you are asking correctly, you will need to modify your score tables to read the info for LOOKUPS. Many of the table have TEXT and not numbers. I attached the one I used. Add this sheet to your spreadsheet

    In your score columns add the following lookup formulas:

    In J5:
    =IF(UPPER($D5)="FEMALE",INDEX(AllScores!$A$3:$A$10 3,MATCH(I5,AllScores!$B$3:$B$103)),INDEX(AllScores !$A$3:$A$103,MATCH(I5,AllScores!$C$3:$C$103)))

    In L5:
    =INDEX(AllScores!$A$3:$A$103,MATCH(K5,AllScores!$D $3:$D$103))

    and in N5:
    =IF(UPPER($D5)="FEMALE",INDEX(AllScores!$A$3:$A$10 3,MATCH(M5,AllScores!$E$3:$E$103,-1)),INDEX(AllScores!$A$3:$A$103,MATCH(M5,AllScores !$F$3:$F$103,-1)))

    These can be copied down the column for all the rows.
    NOTE: the times in column M must be entered as serialtimes enter the hour, colon, minutes, colon and seconds. For example: (no quotes):
    "0:26:35"
    and it will work correctly. Format the cells in col M as
    "[h]:mm:ss" to display correctly (though this will NOT affect the calcs.

    Post back with any other questions

    Steve

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

    kog1994 (2011-03-21)

  6. #5
    New Lounger
    Join Date
    Feb 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Marine Corps PFT Calculator and Tally Sheet (MS Office 2000)

    That work great thank you very much, now there is only one Item left and hopefully you can help me with this. I added to your attachment the age and scoring for what class they would fall under. Some how I would like to by the marines age and total score be able to have it tell me weather or not that person is 1st,2nd,3rd class or Failed. I'm grateful that we have persons out there that can remember all these formulas for us less fortunate ones. LOL <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    If there is anything else you might need please ask, I would put my whole excel file on but last time I did HansV thought it might be confidential information to which it is not.

    Thank You

    Eric

  7. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Marine Corps PFT Calculator and Tally Sheet (MS Office 2000)

    It looked as if the spreadsheet you posted originally contained real names and identification numbers. That was the reason I removed it. But if the information is not confidential, there is no objection to posting the whole file. It must be under 100 K, but if it is larger, you can zip it and post the zip file.

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 340 Times in 333 Posts

    Re: Marine Corps PFT Calculator and Tally Sheet (MS Office 2000)

    Again, if I understand correctly (please check the values):
    Here an updated score sheet. Add this to your workbook (you can replace the other other one.
    In cell Q5 enter the formula:
    =INDEX(AllScores!$L$3:$L$6,MATCH(O5,INDEX(AllScore s!$H$3:$K$6,0,MATCH(C5,AllScores!$H$2:$K$2))))

    Copy it down the columns. It uses the age value and the score to LOOKUP in the table in the worksheet allscores.

    Steve

    PS. Hans already responded about why it was removed. To clarify a little, I was the one who actually raised the question. I was concerned about having names and Social security numbers listed. I did NOT know if they were real or made up. They were not "obviously" made up (values like "123-45-6789" or "111-11-1111") and the names, too did look real so I felt that it might be a concern to have them posted in a non-secure website. If the name/SS# field had been eliminated/changed, it would not have raised a problem.

  9. #8
    New Lounger
    Join Date
    Feb 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Marine Corps PFT Calculator and Tally Sheet (MS Office 2000)

    Thank you both for all your help and yes I do see where the confusion was and I should have stated that the names and SS#'s were made up my bust on that, and I was not angry about it due to the fact I figured that is what happened.
    <img src=/S/chatter.gif border=0 alt=chatter width=38 height=16>
    Eric

  10. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 340 Times in 333 Posts

    Re: Marine Corps PFT Calculator and Tally Sheet (MS Office 2000)

    You didn't repsond about the latest fix. Is this what you were after?

    Steve

  11. #10
    New Lounger
    Join Date
    Feb 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Marine Corps PFT Calculator and Tally Sheet (MS Office 2000)

    I'm sorry got busy here at work, Yes it did fix and I greatly thank you......

Posting Permissions

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