Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Aug 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a USMC PFT Spreadsheet in excel. Need help with VLookup formula.

    I have 3 different work sheets.

    worksheet 1 is pft score (where you type the persons info ie.... gender, age, run time, pull ups, and crunches)

    worksheet 2 is the male score card

    work sheet 3 is the female score card

    So far i am able to compute a score from each worksheet with two seperate cells in worksheet 1. How do I get it to read in just one cell. I am running into the problem with the genders. Here is my formula thus far.....

    =IF(OR(C17="",C17<17,F17=""),"",VLOOKUP(F17,'FEMAL E TABLES'!A3:H193,IF(C17>45,8,ROUNDUP((C17-16)/5+1,0)),TRUE))

    how do i tell it, if B17=Male/Female look on the male/Female scorecard.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Assuming same layout for each card:

    =IF(OR(C17="",C17<17,F17=""),"",VLOOKUP(F17,IF(B17 ="Female",'FEMALE TABLES'!A3:H193,'MALE TABLES'!A3:H193),IF(C17>45,8,ROUNDUP((C17-16)/5+1,0)),TRUE))
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Aug 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks a bunch

    It seems to be working great. Thanks a lot. I just couldn't figure out where to put it at! I am just getting the hang of using excel on a higher level and you have helped out greatly. I do have one more question. Hopefully you can help me out. I used a template formula from a different sheet and in the formula I provided I do not quite understand the back ebd of it. I am hoping you can shed some light on it. I can't figure out what ((C17-16)/5+1,0)),TRUE)) is doing in the formula. I know it makes it work but I would like to understand it better. C17 is where the age is put in. Any assistance would be great. You have already been a great help. Thanks again!

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Looking at the original formula, and ignoring the initial IF test for simplicity, we have:
    VLOOKUP(F17,'FEMALE TABLES'!A3:H193,IF(C17>45,8,ROUNDUP((C17-16)/5+1,0)),TRUE)

    where:
    F17 - lookup value
    'FEMALE TABLES'!A3:H193 - lookup table
    IF(C17>45,8,ROUNDUP((C17-16)/5+1,0)) - column number to return the value from
    TRUE - means we are not looking for an exact match, and the lookup table data is sorted in ascending order (on the first column).

    Now let's examine that third part:
    IF(C17>45,8,ROUNDUP((C17-16)/5+1,0))
    so if the age is greater than 45, we want column 8 in the lookup table.
    If not, subtract 16 (presumably your age ranges start at 17?), divide that by 5 (bands of 5 years in your table) then add 1 and round up to the nearest whole number.
    So, let's say the age is 17:
    17-16 = 1
    1/5 + 1 = 1.2
    round up to next whole number = 2 so we return a value from the second column of the table.

    Make sense?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    New Lounger
    Join Date
    Aug 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It does when you say it like that! Well put. I feel accomplished today! Thanks a bunch for the help. I may be back if I get stuck again, but I am very greatful for the help.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Glad to help.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    New Lounger
    Join Date
    Nov 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    can you possibly email me this excel document so that I am able to get an idea of how to build it. I am in the same boat as you are. Thanks. brandonpaulroy@gmail.com

Posting Permissions

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